vba 将列名从 SQL 查询中提取到 Excel 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4190515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pulling Column Names into Excel from SQL query
提问by firedrawndagger
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.
我正在使用 Excel 从 SQL 数据库中提取数据。我使用了另一个 SO 问题中的代码，它工作正常。现在，除了实际表之外，我还想从表中提取列名。我发现我可以使用 For Each fld 循环获取名称。但是，仍然存在在 Excel 中水平填充它们的问题，因为列数可能会发生变化 - 所以我想我还需要另一个 For each 循环或类似的东西。
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
回答by dendarii
My usual code is very similar:
我通常的代码非常相似：
For intColIndex = 0 To objMyRecordset.Fields.Count - 1
Range("A4").Offset(0, intColIndex).Value = objMyRecordset.Fields(intColIndex).Name
Next
回答by firedrawndagger
Ok so I figured it out after 4 attempts, here's the code for the loop.
好的，所以我在 4 次尝试后想通了，这是循环的代码。
'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
回答by protoculture
To make it super simple, do something like this (using Sheet1 and recordset r)
为了让它超级简单，做这样的事情（使用 Sheet1 和记录集 r）
For i = 0 To r.Fields.Count - 1
Sheet1.Cells(1, i + 1) = r.Fields(i).Name
Next i
回答by Tony Diaz
You can just set your "x" variable to 0 and then do something like:
您可以将“x”变量设置为 0，然后执行以下操作：
x = 0
For Each Field In RS.Fields 'RS being my Recordset variable
Range("A3").Offset(0, x).Value = Field.Name
x = x + 1
Next Field
And that will make it a bit easier to read... :)
这将使它更容易阅读... :)
