首先,我们使用以下方法获取查询的公式:
Debug.Print ThisWorkbook.Queries.Item("Query1").Formula
我们需要这样做的原因是因为公式字符串包含一些关于我们的数据库连接的额外信息,而不仅仅是一个 sql 查询本身。它可能看起来像这样:
let Source = Teradata.Database("tdprod1.ov.de", [HierarchicalNavigation=true, Query="SELECT week, revenue FROM table1"]); in Source
为了更新查询,我们设置ThisWorkbook.Queries.Item("Query1").Formula
为新的公式字符串。在处理 VBA 中的双引号时也要小心。这是我使用的脚本:
' Update the query Query1 using the provided parameters
Sub UpdateQuery1()
' string to represent a single double quote for VBA string
Const vbDoubleQuote As String = """"
Dim oledb As OLEDBConnection
Dim queryFormula As String
' get the connection
Set oledb = ThisWorkbook.Connections("Query - Query1").OLEDBConnection
' the new formula of the query
Let queryFormula = _
"let " & _
"Source = Teradata.Database(" & vbDoubleQuote & "tdprod1.ov.de" & vbDoubleQuote & ", [HierarchicalNavigation=true, Query=" & vbDoubleQuote & _
"SELECT #(lf)a.kw MOD 100 AS KW, #(lf)a.r_Category2_ID AS Category_ID,#(lf)a.r_category3_ID AS Subcategory_ID,#(lf)#(lf)a.vt_p_j AS VP_Jahr,#(lf)a.vt_p_p AS ..." & _
"in Source"
' update the formula of the query
Let ThisWorkbook.Queries.Item("Query1").Formula = queryFormula
' refresh the connection
oledb.Refresh
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句