如何在VBA中获取列名? [英] How to get column name in VBA?
本文介绍了如何在VBA中获取列名?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个模块,其中我正在使用存储过程从SQL DB读取数据,我获得所有必需的数据,但我的要求是,我也需要所有的列名称/标题。我不能硬编码的名称,因为我将存储过程中的数据行转换为列。
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.
这是我写的代码:
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
推荐答案
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
这篇关于如何在VBA中获取列名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文