SQL和Power Pivot:如何在SQL代码中使用Excel中的单元格值

Question

我正在使用Excel 2016和Power Pivot。我有SQL代码来从Oracle数据库中减去一些数据。我的SQL代码位于Power Pivot>Manage>Design>Table Properties中。它运行得很好。我不理解我的设置，但它有效。

但是，我想从Excel工作簿中控制我的代码。例如，假设我的代码如下：

select * 
from my_scheme.my_table
where date = '20200101'

基本上，我希望date变量设置为某个单元格值。我该怎么做？

Answer 1

我也遇到了同样的问题，最后我放弃了powerpivot/powerquery的东西，通过ADODB实现了动态查询

Sub test()
'Microsoft ActiveX Data Objects x.x library required
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Server_Name = "" ' Enter your server name here
Database_Name = "" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "" ' Enter your dynamic SQL here
cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, cn
'rownames
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
    ActiveSheet.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next
'body
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
end test