sql-server - excel 2010 vba mssql 帮助 : can not get table headers (alias)

Question

下面是我的代码，它工作正常，但我有一个小问题。

我有两张不同的表格:在第一张表格中，有一个获取数据的按钮。这是有效的，我可以在第二张表中看到数据。但问题是获取表头。我的代码无法检索标题。

    Sub Add_Results_Of_ADO_Recordset() 
     'This was set up using Microsoft ActiveX Data Components version 2.8

    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stSQL As String 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim rnStart As Range 


    Const stADO As String = "PROVIDER=SQLOLEDB.1;SERVER=192.168.0.300;UID=sa;PWD=sa;DATABASE=sa" 

    'where BI is SQL Database & AURDWDEV01 is SQL Server

    Set wbBook = ActiveWorkbook 
    Set wsSheet = wbBook.Worksheets(1) 

    With wsSheet 
        Set rnStart = .Range("A1") 
    End With 

    stSQL = "SELECT sModel,sKodu,sAciklama FROM tbstok " 

    Set cnt = New ADODB.Connection 

    With cnt 
        .CursorLocation = adUseClient 
        .Open stADO 
        .CommandTimeout = 0 
        Set rst = .Execute(stSQL) 
    End With 

     'Here we add the Recordset to the sheet from A1
    rnStart.CopyFromRecordset rst 

     'Cleaning up.
    rst.Close 
    cnt.Close 
    Set rst = Nothing 
    Set cnt = Nothing 

End Sub

Answer 1

呵呵。修改看起来几乎像我自己的代码感觉很奇怪:)
这会创建标题

Dim i As Long
With rst
    For i = 1 To .Fields.Count
        wsSheet.Cells(1, i) = .Fields(i - 1).Name
    Next i
End With

这将粘贴从 A2 开始的范围

rnStart.CopyFromRecordset rst

rnStart 应设置为 A2 作为记录集开始粘贴的第一个单元格。

Option Explicit

Sub Add_Results_Of_ADO_Recordset()
     'This was set up using Microsoft ActiveX Data Components version 2.8

    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range


    Const stADO As String = "PROVIDER=SQLOLEDB.1;SERVER=192.168.0.300;UID=sa;PWD=sa;DATABASE=sa"

    'where BI is SQL Database & AURDWDEV01 is SQL Server

    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)

    With wsSheet
        Set rnStart = .Range("A2")
    End With

    stSQL = "SELECT sModel,sKodu,sAciklama FROM tbstok "

    Set cnt = New ADODB.Connection

    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With

    ' headers
    Dim i As Long
    With rst
     For i = 1 To .Fields.Count
         wsSheet.Cells(1, i) = .Fields(i - 1).Name
     Next i
    End With

    ' add recordset starting at A2
    rnStart.CopyFromRecordset rst

     'Here we add the Recordset to the sheet from A2
    rnStart.CopyFromRecordset rst

     'Cleaning up.
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

End Sub