sql-server - 使用 SQL Server 中的 Excel VBA 在新行中显示数据

问题描述

我想在一列中显示不同烤箱的最大值（值）。

我尝试处理它，但它正在覆盖下一个 max(value) 并将其输入到同一个单元格中，而不是在列中的不同行中显示它。

Sub TakeDataFromServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim mssql As String
    Dim row As Integer
    Dim Col As Integer
    Dim ws As ThisWorkbook
    Dim i As Integer
    Dim rowCount As Integer
    Dim colCount As Integer

    Set ws = ThisWorkbook

    Application.ScreenUpdating = True

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    startT = Sheet1.Cells(9, 1)
    endT = Sheet1.Cells(9, 5)

    For i = 9 To 12    
        oven = Mid(Sheet1.Cells(i, 9), 3, 1)
        Select Case oven
           Case Is = 1
              mssql = "select * from openquery(ihistorian,'set     starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f1_load* and value <= 750 ')"

           Case Is = 2
              mssql = "select * from openquery(ihistorian,'set starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f2_load* and value <= 750 ')"

           Case Is = 3
              mssql = "select * from openquery(ihistorian,'set starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f3_load* and value <= 750 ')"

           Case Is = 4
              mssql = "select * from openquery(ihistorian,'set starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f4_load* and value <= 750 ')"

           Case Is = 5
              mssql = "select * from openquery(ihistorian,'set starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f5_load* and value <= 750 ')"

           Case Is = 6
              mssql = "select * from openquery(ihistorian,'set starttime=08/07/2018,endtime=08/08/2018,intervalmilliseconds=60000,samplingmode=calculated,calculationmode=maximum select max(Value) from ihrawdata where tagname like fix.f6_load* and value <= 750 ')"

       End Select

       MsgBox oven

       conn.ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=10.1.5.7;UID=sa;PWD=V&53zkd"
       conn.ConnectionTimeout = 30
       conn.Open

       rs.Open mssql, conn

       If rs.EOF Then
          MsgBox "No records found"
          rs.Close
          conn.Close
          Exit Sub
       End If

       row = 8
       Col = 29

       For rowCount = 1 To 4
          For colCount = 1 To 1
             For Each fld In rs.Fields
                Sheet1.Cells(row + rowCount, Col + colCount).Value = fld
             Next
          Next colCount 
       Next rowCount 
       rs.Close
       conn.Close
   Next i

End Sub

我想要这样的输出

但它正在重复更新的数字

标签： sql-serverexcelvba

sql-server - 使用 SQL Server 中的 Excel VBA 在新行中显示数据

问题描述

解决方案

推荐阅读