XLS select statement

mb1824

I have .xls files, each with 1 tab, where the table name is in the format CustomerReport_ddMMyyyyhhmmss

Is it possible to add a wildcard to the SELECT statement like the below (the below doesn't work)so I can input multiple tables without specifying every table name:

SELECT * FROM `CustomerReport_%$`

mb1824

I found a solution, but it relies on the Filename containing the sheet name, which is fine for my use case. 

• I modified the Batch macro idea from this post The Ultimate Input Data to include a Formula before the Batch Macro that creates [Table] as the table name and then configured the Macro to choose [Table] as the control parameter. See below screenshot
• I also modified the Action within the Macro to "Replace a specific string" - see second screenshot. 

So the Macro will replace the specific string "CustomerReport_27022018130050" with whatever appears in [Table]