修改基于特定单元格的SQL查询命令文本

huangapple
go评论114阅读模式
英文:

Modify the command text of an sql query based on certain cells

问题

在一个Excel文件中，我有一个表格，它通过使用SQL查询从外部源检索数据。我想知道如何使用VBA，通过修改同一Excel文件中特定单元格中找到的文本，来刷新这个表格中的数据。

我在考虑只是使用VBA来“重写”命令文本，但我不知道如何将突出显示的元素设为基于其他单元格的变量。

  1.     Sheets("B1 query").Select
  2.     Range("F5").Select
  3.     Application.CutCopyMode = False
  4.     With ActiveWorkbook.Connections("Query from Blueone32").ODBCConnection
  5.         ' 在这里更改命令文本
  6.         .CommandText = Array( _
  7.         "你的SQL查询语句" _
  8.         )
  9.         .CommandType = xlCmdSql
  10.         ' 其他连接设置
  11.     End With
  12.     ' 刷新连接
  13.     ActiveWorkbook.Connections("Query from Blueone32").Refresh
  14.     ' 刷新表格
  15.     With Selection.ListObject.QueryTable
  16.         ' 设置刷新选项
  17.     End With
  18. 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.

修改基于特定单元格的SQL查询命令文本

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.

  1.     Sheets("B1 query").Select
  2.     Range("F5").Select
  3.     Application.CutCopyMode = False
  4.     With ActiveWorkbook.Connections("Query from Blueone32").ODBCConnection
  5.         .BackgroundQuery = True
  6.         .CommandText = Array( _
  7.         "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" _
  8.         , _
  9.         "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" _
  10.         , _
  11.         "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" _
  12.         , _
  13.         "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" _
  14.         , _
  15.         "')" & 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" _
  16.         , _
  17.         ".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" _
  18.         , _
  19.         "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" _
  20.         , _
  21.         "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" _
  22.         , _
  23.         "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" _
  24.         , _
  25.         "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" _
  26.         )
  27.         .CommandType = xlCmdSql
  28.         .Connection = Array(Array( _
  29.         "ODBC;DRIVER=CONNECTION_STRING" _
  30.         ), Array( _
  31.         "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;" _
  32.         ), Array("MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;"))
  33.         .RefreshOnFileOpen = False
  34.         .SavePassword = True
  35.         .SourceConnectionFile = ""
  36.         .SourceDataFile = ""
  37.         .ServerCredentialsMethod = xlCredentialsMethodIntegrated
  38.         .AlwaysUseConnectionFile = False
  39.     End With
  40.     With ActiveWorkbook.Connections("Query from Blueone32")
  41.         .Name = "Query from Blueone32"
  42.         .Description = ""
  43.     End With
  44.     ActiveWorkbook.Connections("Query from Blueone32").Refresh
  45.     With Selection.ListObject.QueryTable
  46.         .RowNumbers = False
  47.         .FillAdjacentFormulas = False
  48.         .PreserveFormatting = True
  49.         .RefreshOnFileOpen = False
  50.         .BackgroundQuery = True
  51.         .RefreshStyle = xlInsertDeleteCells
  52.         .SavePassword = True
  53.         .SaveData = True
  54.         .AdjustColumnWidth = True
  55.         .RefreshPeriod = 0
  56.         .PreserveColumnInfo = True
  57.     End With
  58. End Sub

答案1

得分: 0

使用参数化查询。不要使用AND tev.lps_label IN ('W-AVSEC')，而是使用

  1. AND tev.lps_label IN (?)

（尽管为单个值使用"IN"效率更高）。然后使用ODBC参数传递所需的值，例如（警告，未经测试的代码）

  1. Dim cmd As ADODB.Command
  2. Set cmd = New ADODB.Command
  3.  With cmd
  4.     .ActiveConnection = cn
  5.     .CommandText = sql
  6.     .CommandType = adCmdText
  7. End With
  8. Dim parm() As ADODB.Parameter
  9. ReDim parm(0) As ADODB.Parameter
  11. Set parm(0) = .CreateParameter("lpsName", adVarChar, adParamInput)
  12. parm(0).Value = ws.Range(x).Value '您的范围
  13. parm(0).Size = 255     '注意，adVarChar参数类型必须指定大小
  14. Dim i As Integer
  15. For i = 0 To UBound(parm) - 1
  16.    cmd.Parameters.Append parm(i)
  17. Next

此外，所有那些Chr(13) & "" & Chr(10)可以替换为VbCrLf - 尽管根本没有必要 - 代码不需要看起来漂亮，不是为了人类阅读。

英文:

Use a paramterised query. Instead of AND tev.lps_label IN ('W-AVSEC') use

  1. 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)

  1. Dim cmd As ADODB.Command
  2. Set cmd = New ADODB.Command
  3.  With cmd
  4.     .ActiveConnection = cn
  5.     .CommandText = sql
  6.     .CommandType = adCmdText
  7. End With
  8. Dim parm() As ADODB.Parameter
  9. ReDim parm(0) As ADODB.Parameter
  11. Set parm(0) = .CreateParameter("lpsName", adVarChar, adParamInput)
  12. parm(0).Value = ws.Range(x).Value 'whatever your range is
  13. parm(0).Size = 255     'NB Size in mandatory for adVarChar parameter types
  14. Dim i As Integer
  15. For i = 0 To UBound(parm) - 1
  16.    cmd.Parameters.Append parm(i)
  17. 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

huangapple
  • 本文由 发表于 2023年3月15日 18:41:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/75743587.html
  • excel
  • sql
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定