使用SQL查询中的列别名作为Excel中的列标题 [英] Use column alias from SQL query as column header in Excel

问题描述

我具有以下VBA，可以使用此VBA中的SQL语句从数据库获取数据:

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.

但是，现在我还希望 SQL 中 cID 的别名在Excel文件中显示为 column header .
结果应如下所示:

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

因此，我尝试使用这里，然后在我的VBA中输入代码:

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

VBA正在运行.但是，别名不会显示为列名.
我需要在VBA中进行哪些更改才能使其正常工作?

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?

推荐答案

尝试使用此字段名再获得一个ROW:

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

这篇关于使用SQL查询中的列别名作为Excel中的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！