如何获取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 SubI 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更多推荐
发布评论