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

Salman Shamim

New Member
Joined
Jun 15, 2017
Messages
4
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
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

New Member
Joined
Jun 15, 2017
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,821
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top