Using VBA how to change the backend query definition of a table in Excel

[Salman Shamim]

Hello Dear All,

There is a table in excel 2016 made out of a SQL Query which is linked to a few pivot tables, each month we open the SQL query in Query Editor and change the parameters and save. Later we refresh all the pivot tables to update.

My Question is: Is there a way to change or update that SQL Query in VBA like inputting dates in a cell and pressing a button, where the parameter is updated and the table is refreshed.

We use excel 2016 and the database is SQL SERVER 2014.


Your assistance will be highly appreciated and will be a lifesaver.

 

[mole999]

You can use cells to feed the query

something rough I use

EMPLOYEEID = Sheets("driver hx").Range("a6").Value 'Cells(7, 1).Value
getuser = EMPLOYEEID

Var1 = Var1 & "where ( a.personnel = '" & getuser & "' ) " & vbCrLf

 

[Salman Shamim]

I used to do that in Excel 2007 however with a query in place via excel 2016 query editor i am stumped on how to do it ? and even if i use the connection string methodology i need to update a certain table in the workbook so all associated pivot tables are refreshed.