I am writing a macro for uploading data from the database directly to the sheet.

Sub Load_longer_period() Dim conn As ADODB.Connection Dim cmd As ADODB.Command Set conn = New ADODB.Connection Set cmd = New ADODB.Command login = ... pass = ... conn.open "Provider=OraOLEDB.Oracle.1;Password=" & pass & ";Persist Security Info=True;User ID=" & login & ";Data Source=..." conn.BeginTrans cmd.ActiveConnection = conn cmd.CommandText = "select * from test_table" Set rs = cmd.Execute ActiveWorkbook.Sheets("Договоры").Range("A1").CopyFromRecordset rs Set cmd = Nothing Set conn = Nothing Application.ScreenUpdating = True End Sub

But this macro unloads the result without the name of the columns themselves, only the data itself. How to get more in addition and column names?

    2 answers 2

    The table that we get after the query is executed is run in a loop, using .Name we get the column names. The code is about the following.

     Set rsResponses = cmdSQL.Execute('YourCode') Worksheets("Sheet1").Range("A2").CopyFromRecordset rsResponses For i = 1 To rsResponses.fields.Count Worksheets("Sheet1").Cells(i, 1).Value = rsResponses.fields(i).Name Next i

    As an alternative, in the form of a crutch :) Request pull out the column names. And then either UNION ALL to the data from the main query (you need to check for the correspondence of the column types in the query with the names and data), or in the macro, execute the query separately and insert before the data

     select s.name from sys.columns s join sys.tables x on s.object_id = x.object_id where x.name = 'table_name'
    • I think this is a bit of a crutch decision, but as a backup, if anything comes down. - Denis
    • Googled seems to offer a more “direct” solution mrexcel.com/forum/excel-questions/… - T.Zagidullin
    • Yes that's right. Move the code from the link to the answer, I will mark as correct. - Denis

    ... the macro unloads the result without the name of the columns themselves, only the data itself.
    How to get more in addition and column names? ...

    Use QueryTable , get the result with column names.

     Sub Get_Dataset() Dim cn As ADODB.Connection: Set cn = New ADODB.Connection Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset Dim strCon$, strSQL$, login$, pass$, QT As QueryTable login = "your login" pass = "your password" strCon = "Provider=OraOLEDB.Oracle.1;Password=" & pass & ";Persist Security Info=True;User ID=" & login & ";Data Source=..." strSQL = "select * from test_table" cn.Open strCon rs.Open strSQL, cn With Sheets("Договоры") Set QT = .QueryTables.Add(rs, .[A1]) QT.Refresh QT.Delete rs.Close cn.Close End With End Sub