从SQL查询将列名称拉入Excel [英] Pulling Column Names into Excel from SQL query

问题描述

我正在使用Excel从SQL数据库中提取数据。我使用另一个SO问题的代码，它的工作正常。现在我想从表中拉入列名，除了实际的表。我发现我可以使用For Each fld循环得到这些名字。然而，仍然存在在Excel中一行一行地填充它们的问题，因为列数可能会改变 - 所以我认为我将需要另一个对于每个循环也可以类似的。

I'm using Excel to pull data from an SQL db. I used the code from another SO question and it works fine. Now I want to pull in the column names from a table in addition to the actual table. I figured out that I could get the names using the For Each fld loop. However there's still the issue of populating them horizontally in a row in Excel as the number of columns might change - so I'm thinking I would need another For each loop also or something similar.

Sub GetDataFromADO()

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"
    objMyConn.Open

'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = "select * from myTable"
    objMyCmd.CommandType = adCmdText
    objMyCmd.Execute

'Loop Names'
    ' WHAT TO DO HERE????'

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open objMyCmd

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

推荐答案

我尝试了4次尝试后，这是循环的代码。

Ok so I figured it out after 4 attempts, here's the code for the loop.

 'Loop'
 Dim FieldRange As Range
 Set FieldRange = Range("A4")
 Set TableColumns = Range("A4:H4")
 x = 1

 Range("A4").Select

 For Each fld in objMyRecordset.Fields
      ActiveCell.Value = fld.Name
      ActiveCell.Offset(0, x).Select
      x = x + 1 'tick iterator
 Next

 ActiveSheet.Range("A5").CopyFromRecordset objMyRecordset
 Range("A4").Select

这篇关于从SQL查询将列名称拉入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！