Link to home
Start Free TrialLog in
Avatar of Mehawitchi
Mehawitchi

asked on

VBA Code for Using A Parameterized Query to SQL Database From Within Excel

Hello experts,

I have an Excel table that reads from a table in SQL database through a data connection embedded in the Excel file itself.

I need to convert the static "Select" statement to make it parameterized:

Select col1, col2, col3 From mySqlTable where @myParam1 = "UserSuppliedValue"

I need to do this from Excel, so I'm looking for VBA code that would set the connection string based on value supplied by user (possibly from a text box or dropbox on Excel sheet), and then retreive the data from SQL and refreshing my Excel table.

Any help is greatly appreciated,
Hani
SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mehawitchi
Mehawitchi

ASKER

Thanks CodeCruiser

I wish it could be that simple. I'm actually looking for VBA code for accessing the connection string propertiy of the DataConnection and then run the query.
Basic syntax is:

activeworkbook.Connections(1).OLEDBConnection.CommandText = "Select col1, col2, col3 From mySqlTable where SomeField = '" & Sheets("Sheet1").Range("A1").Value & "'"

Open in new window


for example.
Thanks rorya - This is a step in the right direction.

I was hoping for complete code.

Dim myCon As OLEDBConnection
Set myCon = New OLEDBConnection

myCon.CommandText = "SELECT Weekday_Name FROM ctvTrackerDW.dbo.DimDateMonthly"

myCon.Refresh
...

Right now I'm getting error trying to establish a new connection because the existing connection was made for table, not SQL statment.

Should I change CommendType?

I'm stuck and would appreciate a complete example...

Thanks again,
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again rorya - It worked beautifully.

One more question if possible: Is there anyway I can create new connection on the fly during run, rather than using the the already embeded one?
It's basically:

   Dim oConn As WorkbookConnection
   Set oConn = ActiveWorkbook.Connections.Add("some name", "Description", "connection string here", "SQL string here", xlCmdSql)

Open in new window


Note that the connection string needs to start with "OLEDB;" or "ODBC;" depending on the connection type you want.
Thank you very much rorya