从SQL查询将列名称拉入Excel

本文介绍了从SQL查询将列名称拉入Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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