Connection Commnd text / variable based on lookup table

Hi. I hope someone can help me with this one.

I currently have a 'Reports Updater' workbook, which is just contains a macro button that when pressed, opens all .xlsm files in a specific folder ("Updating folder"), then does a 'Refresh All' on pivots (Pivot tables linked via a OLEDB connection), saves and closes.

Works fine, here's the code:

Please Login or Register  to view this content.

This is a slightly simplified example. I actually have multiple 'buttons'/macros, that do various things, but the example above is the most frequently used.

However I'm looking to do something slightly different, and not sure how (or if it's possible).
I want to open all the workbooks as above, but change each file's connection string 'command text' - based on a reference table in the 'Reports Updater' workbook.

So for example in the Reports Updater workbook, I'd have a tab called 'connections'. In 'connections' worksheet in column A id have the list of workbook names in 'Updating folder' (eg, "Market Summary - All", "Market Summary - UK" "Market Summary - USA" etc...) , and in Column B I'd have the corresponding connection string command text (eg '"qry_All", "qry_UK", "qry_USA", etc...).

Any ideas on if/how to do this? I'm fairly sure it can be done, just not sure how to set the variable for the command text based on the table?

I would be grateful of any help.

Thanks

The following wiki addresses MS-Query, however there is a section that explains how to use an Excel Table to manage SQL and write it back to the command text. The SQL statement is contained in two columns in the table. The first column is the "Raw" SQL that contains the "boiler plate" plus some tokens like &START_DATE and &END_DATE where a substitution is needed. The second column in the table does the actual substitution. Most of the time, I am merely substituting a calculated date, but I've also used it to switch tables (one table has end of month data, the other mid-month data - both have the same schema) and have written parts of where clauses such is generating a dynamic list of models in an "IN" statement.

The substitution technique should work on the command string regardless if it was created my MS-Query. I've not had the opportunity to test this but it's a theory that should work.

This isn't the best wiki I've ever written, so if you have questions, please ask.

http://www.utteraccess.com/wiki/MS_Query

Upon further reading the descriptions, I am almost certain it will work for what you want to do. If you don't need to substitute the command text (I assume it may be the same for all queries) - just look at the SubSQL routine in the included code.

In fact, this single line should do it for you:

Please Login or Register  to view this content.

where xlS is the open workbookand ConnectionName comes from your table.

Thanks for your suggestion.

I managed to do it in the end as per below:

Please Login or Register  to view this content.