Update query with multiple worksheets

**jonas-martens** · 05-24-2017, 03:23 AM

Hi All,

I have a worksheet called "Actuals" with a query on it.
The source behind the query is an excel file with multiple worksheets by week ex. W1, W2, W3, ...

For example in week 3, I want to see worksheets W1, W2 and W3.
I go to my ribbon in excel: Data > connections > properties > Definition > Command text

There I type :

SELECT `W1$`.*
FROM `XXXXXXX.xlsm`.`W1$` `W1$`
Union all
SELECT `W2$`.*
FROM `XXXXXXX.xlsm`.`W2$` `W2$`
Union all
SELECT `W3$`.*
FROM `XXXXXXX.xlsm`.`W3$` `W3$`

Is there a possibility to update this automatically with a macro?
So if I push a button, another week is added.

Thanks in advance.
If anyone would like an example, feel free to ask.

kr Jones

**dflak** · 05-24-2017, 01:43 PM

Here is something that uses one of my "standard" modules. I generally use this with an Oracle database, but it should also work with any data connection.

Go to Data -> Connections and get (or change) the name of your connection.

Copy the code into a module in your workbook. Also copy the SQL sheet into your workbook. Put the name of your connection in place of qry_data in the following code.

Please Login or Register  to view this content.

Enter the week number in Cell E4 on the SQL sheet. Run the NewWeek macro. This macro adds the current week SQL to the table. Cell A1, AKA "My_SQL," contains a concatenated string of the SQL commands created by the Supercat function. The subroutine SubSQL replaces the existing command string for the connection with the new SQL.

SubSQL is a powerful tool. You can use all kinds of formulas to generate SQL "on the fly."