问题描述
我具有以下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中的列标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!