Connecting to multiple named ranges in a single workbook

[NickonPhil]

Hi all,
I am trying to connect to 'named ranges' on separate worksheets in multiple workbooks (each workbook has the same content / layout / sheets / named ranges), and I am pointing 'get data' to the directory containing the workbooks, for example:

Get Data = C:\My Books\ in the My Books directory is a set of Excel workbooks named Jan 2018.xlsx, Feb 2018.xlsx, Mar 2018.xlsx etc.
Each workbook has 3 worksheets - call them Fruit, Veg, Meat, and each worksheet has a 'named range' (I'd like to convert them to tables in the worksheets but due to formatting in the worksheets outside of my control I can't do that). So let's say we have worksheet Fruit with a named range 'fruit_data', a worksheet Veg with a named range 'veg_data', and a worksheet Meat with a named range 'meat_data.

I would like to be able to load the 'named ranges' from each worksheet, in each workbook into it's own table, appending the corresponding named ranges from the other worksheets, but also add the filename as a column in each table created like the below example for the fruit_data named range:

Table = fruit_data

Source.Name	Product	Qty	Price
Jan 2018	Apples	100	5.00
Jan 2018	Oranges	500	11.00
Feb 2018	Apples	300	12.00
Mar 2018	banana's	120	15.00

<tbody>
</tbody>

A similar table for the meat_data named range:
Table = meat_data

Source.name	Product	Weight (kg)	Price
Jan 2018	Beef	3.00	24.00
Feb 2018	Lamb	8.00	46.00
Feb 2018	Chicken	18.00	102.00
Mar 2018	Beef	7.00	44.00

<tbody>
</tbody>

Is this possible? Can anybody provide instructions to accomplish the above?

Thanks
Phil

 

[VBA Geek]

You need to make 3 queries, one for each tab name. If you use the "From Folder" import feature this should be fairly easy normally just using the UI.