vba 如何在VBA中获取列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7150054/
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
How to get column name in VBA?
提问by Safran Ali
I have created a module where I am reading the data from SQL DB using a stored procedure and I am getting all the required data, but my requirement is that I also needs all the column name/headers too. And I can't hardcode the name because I am converting the data rows into columns in stored procedure.
我创建了一个模块，我在其中使用存储过程从 SQL DB 读取数据并获取所有必需的数据，但我的要求是我还需要所有列名/标题。而且我无法对名称进行硬编码，因为我将数据行转换为存储过程中的列。
Here is the code I have written:
这是我写的代码：
ConnStr = "PROVIDER=SQLOLEDB.1;"
ConnStr = ConnStr & "DATA SOURCE=" & dataSrc & "; INITIAL CATALOG=" & iCatalog & "; "
ConnStr = ConnStr & "User ID = " & dbUserId & "; Password = " & dbPassword
On Error GoTo ErrHandler
DBConn.Open ConnStr
' Create a recordset object.
Dim rsCounter As ADODB.Recordset
Set rsCounter = New ADODB.Recordset
Dim startDate As String, endDate As String, query As String
query = "Exec ReadCntrs 0, '12/01/2011', '12/30/2011'"
With rsCounter
' Assign the Connection object.
.ActiveConnection = DBConn
.Open query
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsCounter
' Tidy up
.Close
End With
DBConn.Close
Set rsCounter = Nothing
Set DBConn = Nothing
ExitHere:
On Error Resume Next
DBConn.Close: Set DBConn = Nothing
Err.Clear
Exit Sub
ErrHandler:
MsgBox Err.Number & Err.Description, vbExclamation
Resume ExitHere
End Sub
回答by onedaywhen
With rsCounter
' Assign the Connection object.
.ActiveConnection = DBConn
.Open query
' Write the column names into cell A1 on Sheet1.
Dim counter As Long
For counter = 0 To rsCounter.Fields.Count - 1
Sheet1.Range("A1").Offset(, counter).Value = rsCounter.Fields(counter).Name
Next
' Copy the records into cell A2 on Sheet1.
Sheet1.Range("A2").CopyFromRecordset rsCounter
' Tidy up
.Close
End With
回答by transistor1
rsCounter.Fields(i).Name-- where i is an integer representing the field number-- is normally how you access the field name.
rsCounter.Fields(i).Name-- 其中 i 是表示字段编号的整数 -- 通常是您访问字段名称的方式。
EDIT - also, to get the total number of fields you will use rsCounter.Fields.Count
编辑 - 另外，获取您将使用的字段总数 rsCounter.Fields.Count
