SQL and Power Pivot: How Can I in my SQL code use cell values in Excel?

0
votes

I am using Excel 2016 and Power Pivot. I have SQL code to subtract some data from an Oracle databases. My SQL code is in Power Pivot > Manage > Design > Table Properties. It is running well. I don't understand my setup, but it works.

However I want to control my code from my Excel workbook. For instance assume my code is like:

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

Basically I want the date variable to be set to a certain cell value. How can I do that?

1
sqlexceloraclepowerpivotexcel-2016

1 Answers

0
votes

I had the same issue and finally I gave up powerpivot/powerquery stuff and implemented dynamic queries by ADODB.Connection like:

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