使用带有标题的 vba 将 sql 查询结果导出到 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24282620/
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
Export sql query results to excel using vba with headers
提问by sk8er_boi47
I need to export a simple SQL query result to excel. I am able to get the results, however the headers are missing, how can I export the headers as well?
我需要将一个简单的 SQL 查询结果导出到 excel。我能够得到结果，但是标题丢失了，我怎样才能导出标题？
This is what i have so far:
这是我到目前为止：
Sub Conn2SQL()
Dim cnn1 As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};server=MyDBServer;uid=MyuserID;pwd=mypassword;database=MyDB"
cnn1.ConnectionTimeout = 30
cnn1.Open
SQry = "use MyDB select * from TableName"
mrs.Open SQry, cnn1
Sheet2.Range("A2").CopyFromRecordset mrs
mrs.Close
cnn1.Close
End Sub
回答by Gareth
You need to loop through the field names to include the headers. An example from the Microsoft site is below:
您需要遍历字段名称以包含标题。来自 Microsoft 站点的示例如下：
For iCols = 0 To oRS.Fields.Count - 1
Sheet(1).Cells(1, iCols + 1).Value = oRS.Fields(iCols).Name
Next
So to implement in your code, it would be this:
因此，要在您的代码中实现，将是这样的：
Sub Conn2SQL()
Dim cnn1 As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim iCols As Integer
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};server=MyDBServer;uid=MyuserID;pwd=mypassword;database=MyDB"
cnn1.ConnectionTimeout = 30
cnn1.Open
SQry = "use MyDB select * from TableName"
mrs.Open SQry, cnn1
For iCols = 0 To mrs.Fields.Count - 1
Worksheets("Sheet2").Cells(1, iCols + 1).Value = mrs.Fields(iCols).Name
Next
Sheet2.Range("A2").CopyFromRecordset mrs
mrs.Close
cnn1.Close
End Sub
回答by MFranco
There is another way to do this. You can add your SQL connection and table data to a sheet. (Excel data connection wizard)
还有另一种方法可以做到这一点。您可以将 SQL 连接和表数据添加到工作表中。（Excel数据连接向导）
Then use your VBA to delete the data in that Table, and insert your data via code (create SQL connection and query using VBA).
然后使用您的 VBA 删除该表中的数据，并通过代码插入您的数据（使用 VBA 创建 SQL 连接和查询）。
Once your code is successfully deleting the data and requerying your SQL data into that table, you can delete the connection created.
一旦您的代码成功删除数据并将您的 SQL 数据重新查询到该表中，您就可以删除创建的连接。
You will be left with a Table with Column headers and your data will now dynamically be brought in and dumped into the table. No column heading VBA required. :)
您将留下一个带有列标题的表，您的数据现在将被动态引入并转储到表中。不需要列标题 VBA。:)
