Use column alias from SQL query as column header in Excel

Question

I have the following VBA to get datas from the database using the SQL statement in this VBA:

Sub ConnectDB5()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dateVar As Date

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

    strSQL = " SELECT " & _
                " cID AS Campaign " & _
                " FROM PDW_DIM_Offers_Logistics_history " & _
                " WHERE DATE(insert_timestamp) = ""2020-02-24"" "

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet4.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

All this works perfectly.

However, now I also want that the alias for the cID in the SQL is displayed as column header in the Excel file.
The result should look like this:

            A               B            C
1        campaign
2         001
3         002
4         003
5          :
6          :
7

Therefore, I tried to go with the solution from here and entered the code in my VBA:

Sub ConnectDB6()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dateVar As Date

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

    strSQL = " SELECT " & _
                " cID AS Campaign " & _
                " FROM PDW_DIM_Offers_Logistics_history " & _
                " WHERE DATE(insert_timestamp) = ""2020-02-24"" "

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

        For iCols = 0 To rs.Fields.Count - 1
        Sheet4.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
        Next

    Sheet4.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

The VBA is running through. However, the alias is not displayed as column name.
What do I need to change in my VBA to make it work?

Thanks for the hint. I edited my question. I do not get the runtime error anymore but the alias is still not used as column name. — Michi
– Michi, Commented Mar 5, 2020 at 14:14

Bernd Buffen · Accepted Answer · 2020-03-05 14:16:24Z

0

Try this to get one ROW more with the fieldname:

strSQL = " SELECT 'campaign'  UNION ALL SELECT " & _
            " cID AS Campaign " & _
            " FROM PDW_DIM_Offers_Logistics_history " & _
            " WHERE DATE(insert_timestamp) = ""2020-02-24"" "

answered Mar 5, 2020 at 14:16

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michi Over a year ago

Thanks a lot. Exactly what I needed.

Michi Over a year ago

Do you also have an idea for this: stackoverflow.com/questions/60547858/…

Michi Over a year ago

Not really, see the comment below his question.

Collectives™ on Stack Overflow

Use column alias from SQL query as column header in Excel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related