如何获取VBA中的列名？(How to get column name in VBA?)

如何获取VBA中的列名？(How to get column name in VBA?)

我创建了一个模块，在这里我使用存储过程从SQL DB读取数据，并获取所有必需的数据，但是我的要求是我也需要所有的列名/头文件。 而且我无法对名称进行硬编码，因为我正在将数据行转换为存储过程中的列。

这是我写的代码：

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

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.

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

最满意答案

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 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