英文:
Modify the command text of an sql query based on certain cells
问题
在一个Excel文件中，我有一个表格，它通过使用SQL查询从外部源检索数据。我想知道如何使用VBA，通过修改同一Excel文件中特定单元格中找到的文本，来刷新这个表格中的数据。
我在考虑只是使用VBA来“重写”命令文本，但我不知道如何将突出显示的元素设为基于其他单元格的变量。
Sheets("B1 query").Select
Range("F5").Select
Application.CutCopyMode = False
With ActiveWorkbook.Connections("Query from Blueone32").ODBCConnection
' 在这里更改命令文本
.CommandText = Array( _
"你的SQL查询语句" _
)
.CommandType = xlCmdSql
' 其他连接设置
End With
' 刷新连接
ActiveWorkbook.Connections("Query from Blueone32").Refresh
' 刷新表格
With Selection.ListObject.QueryTable
' 设置刷新选项
End With
End Sub
请将上面的"你的SQL查询语句"替换为你想要使用的SQL查询语句，然后在VBA中使用这个代码来刷新表格中的数据。
英文:
In an excel file, I have a table which is retrieving data from an external source using a sql query. I am wondering how to, using VBA, refresh this table by modifying the highlighted element based on the text found in a specific cell in the same excel file.
I was thinking of just using vba to "rewrite" the command text but I do not know how to make the highlighted element a variable based on that other cell.
Sheets("B1 query").Select
Range("F5").Select
Application.CutCopyMode = False
With ActiveWorkbook.Connections("Query from Blueone32").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT DISTINCT cre.cre_id" & Chr(13) & "" & Chr(10) & ", cre.cre_HR_ID" & Chr(13) & "" & Chr(10) & ", cre.CRE_ALPHA" & Chr(13) & "" & Chr(10) & ", cre.CRE_LAST_NAME" & Chr(13) & "" & Chr(10) & ", cre.CRE_FIRST_NAME" & Chr(13) & "" & Chr(10) & ", pos.PT_L" _
, _
"ABEL" & Chr(13) & "" & Chr(10) & "-- , CASE " & Chr(13) & "" & Chr(10) & "-- WHEN PT_LABEL LIKE '%CPT%' THEN 'CPT'" & Chr(13) & "" & Chr(10) & "-- WHEN PT_LABEL LIKE '%FO%' THEN 'FO'" & Chr(13) & "" & Chr(10) & "-- ELSE PT_LABE" _
, _
"L" & Chr(13) & "" & Chr(10) & "-- END FUNCTIE" & Chr(13) & "" & Chr(10) & ", gco.gco_type" & Chr(13) & "" & Chr(10) & ", tev.lps_label" & Chr(13) & "" & Chr(10) & ", gco.gco_start" & Chr(13) & "" & Chr(10) & "--, gco.gco_end" & Chr(13) & "" & Chr(10) & "-- , gco.gco_end-gco.gco_star" _
, _
"t" & Chr(13) & "" & Chr(10) & ", to_char(gco.gco_start,'RRRR')" & Chr(13) & "" & Chr(10) & "-- , to_char(gco.gco_start,'HH24:MI:SS')" & Chr(13) & "" & Chr(10) & "-- , to_char(gco.gco_end,'DD-MON-RRRR" _
, _
"')" & Chr(13) & "" & Chr(10) & "-- , to_char(gco.gco_end,'HH24:MI:SS')" & Chr(13) & "" & Chr(10) & "-- , gco.gco_end-gco.gco_start" & Chr(13) & "" & Chr(10) & "FROM master.v_assignments asg" & Chr(13) & "" & Chr(10) & ", master" _
, _
".v_crews cre" & Chr(13) & "" & Chr(10) & ", MASTER.V_POSITIONS pos" & Chr(13) & "" & Chr(10) & ", master.v_training_events tev" & Chr(13) & "" & Chr(10) & ", master.v_ground_courses gco" & Chr(13) & "" & Chr(10) & "WHERE cre.CR" _
, _
"E_ID = asg.ASG_CRE_ID (+)" & Chr(13) & "" & Chr(10) & "AND asg.ASG_POS_ID = pos.POS_ID" & Chr(13) & "" & Chr(10) & "and asg.asg_id = tev_asg_id" & Chr(13) & "" & Chr(10) & "and gco.gco_id = asg.asg_g" _
, _
"co_id " & Chr(13) & "" & Chr(10) & "AND (CRE.CRE_ALPHA like '2%' OR (LENGTH(CRE.CRE_ALPHA) = 3))" & Chr(13) & "" & Chr(10) & "AND cre.CRE_ACTIF='Y'" & Chr(13) & "" & Chr(10) & "AND gco.gco_end<to_dat" _
, _
"e('10MAY2023 23:59:00','DDMONYYYY HH24:MI:SS')" & Chr(13) & "" & Chr(10) & "AND gco.gco_start>to_date('30oct2022 00:00:00','DDMONYYYY HH24:MI:S" _
, _
"S')" & Chr(13) & "" & Chr(10) & "AND asg.ASG_D_TYPE = 'GCO'" & Chr(13) & "" & Chr(10) & "AND tev.lps_label IN ('W-AVSEC')" & Chr(13) & "" & Chr(10) & "ORDER BY gco.gco_start, cre.CRE_ALPHA" _
)
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DRIVER=CONNECTION_STRING" _
), Array( _
"LUEONE;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;" _
), Array("MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;"))
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from Blueone32")
.Name = "Query from Blueone32"
.Description = ""
End With
ActiveWorkbook.Connections("Query from Blueone32").Refresh
With Selection.ListObject.QueryTable
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
End Sub
答案1
得分: 0
使用参数化查询。不要使用AND tev.lps_label IN ('W-AVSEC')，而是使用
AND tev.lps_label IN (?)
（尽管为单个值使用"IN"效率更高）。然后使用ODBC参数传递所需的值，例如（警告，未经测试的代码）
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = sql
.CommandType = adCmdText
End With
Dim parm() As ADODB.Parameter
ReDim parm(0) As ADODB.Parameter
Set parm(0) = .CreateParameter("lpsName", adVarChar, adParamInput)
parm(0).Value = ws.Range(x).Value '您的范围
parm(0).Size = 255 '注意，adVarChar参数类型必须指定大小
Dim i As Integer
For i = 0 To UBound(parm) - 1
cmd.Parameters.Append parm(i)
Next
此外，所有那些Chr(13) & "" & Chr(10)可以替换为VbCrLf - 尽管根本没有必要 - 代码不需要看起来漂亮，不是为了人类阅读。
英文:
Use a paramterised query. Instead of AND tev.lps_label IN ('W-AVSEC') use
AND tev.lps_label IN (?)
(Although why use "IN" for a single value, = would be more efficient). Then use an ODBC Paramter to pass the value you want e.g. (warning, untested code)
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = sql
.CommandType = adCmdText
End With
Dim parm() As ADODB.Parameter
ReDim parm(0) As ADODB.Parameter
Set parm(0) = .CreateParameter("lpsName", adVarChar, adParamInput)
parm(0).Value = ws.Range(x).Value 'whatever your range is
parm(0).Size = 255 'NB Size in mandatory for adVarChar parameter types
Dim i As Integer
For i = 0 To UBound(parm) - 1
cmd.Parameters.Append parm(i)
Next
Also - all those Chr(13) & "" & Chr(10) can be replaced with VbCrLf - not that there is any need for them at all - the code does not need to look pretty, it's not a human reading it
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论