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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Source.Name[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Jan 2018[/TD]
[TD]Apples[/TD]
[TD]100[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Jan 2018[/TD]
[TD]Oranges[/TD]
[TD]500[/TD]
[TD]11.00[/TD]
[/TR]
[TR]
[TD]Feb 2018[/TD]
[TD]Apples[/TD]
[TD]300[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]Mar 2018[/TD]
[TD]banana's[/TD]
[TD]120[/TD]
[TD]15.00[/TD]
[/TR]
</tbody>[/TABLE]

A similar table for the meat_data named range:
Table = meat_data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Source.name[/TD]
[TD]Product[/TD]
[TD]Weight (kg)[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Jan 2018[/TD]
[TD]Beef[/TD]
[TD]3.00[/TD]
[TD]24.00[/TD]
[/TR]
[TR]
[TD]Feb 2018[/TD]
[TD]Lamb[/TD]
[TD]8.00[/TD]
[TD]46.00[/TD]
[/TR]
[TR]
[TD]Feb 2018[/TD]
[TD]Chicken[/TD]
[TD]18.00[/TD]
[TD]102.00[/TD]
[/TR]
[TR]
[TD]Mar 2018[/TD]
[TD]Beef[/TD]
[TD]7.00[/TD]
[TD]44.00[/TD]
[/TR]
</tbody>[/TABLE]

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.