Getting external data from Dynamic Path and Closed Files

[voodoo1]

Hello,

I'm trying to get external data from a number of different excel files across different Locations, WITHOUT needing to open the files.

I want to aggregate all data from one year (on different Closed workbooks) into a single sheet (per store) on the Open Workbook .

The Closed woorkbooks are organized per location, as follows:
» Name_dir / Store_Name / Year / Month / File_name.ods

Name_dir -> root folder of all files
* All files have the same structure and are identical.

------------------
So, I started by creating a relation for each Store code and Folder name so I can refer easly to the Folder Path / Data File.

In the Store Sheet (Open workbook) - where I want the data to be imported to - I have a date on each collumns header, which allow me to refer to the correspondent file of the month and year.

I started to use INDIRECT() formula, before I learned it dont work with closed files.

=INDIRECT("'"&D$83&"MENSAL'!D"&ROW()&":ZZ"&ROW())
&D$83 -> path to the correspondent data file, based on the date.

Example of the output:

D:\......\STORE NAME\2019\09-Setembro\[Folha de Caixa.ods]

Also, I have combined INDIRECT with SUMIFS, because I need two parameters to be confirmed (date; luch/dinner time), as follows:
=SUMIFS(
INDIRECT("'"&D$83&"MENSAL'!D"&ROW()&":ZZ"&ROW());
INDIRECT("'"&D$83&"MENSAL'!D1:ZZ1");D$1;
INDIRECT("'"&D$83&"MENSAL'!D2:ZZ2");D$2)

D$1 -> Date
D$2 -> Lunch/dinner

So, I'am SUMIFS()'ing all the values from the correspondent row() if the date D1:ZZ1 matches D$1, and if the Lunch/dinner on D2:ZZ2 matches D$2.

IT ALL WORKED PERFECTLY.

However, I dont want, and cant open all the files to refresh the data, as it is not efficient. Therefore, since the INDIRECT() dont work with closed documents, i'm looking for a SOLUTION: function to open those files, preferably on background, update the information, and close the files.

I also tryed INDIRECT.EXT() from Morefunct, as I was looking for a solution.
I'm currently trying to use PULL function by Harlan Grove, still with no solutions..

I would be much appreciated if someone could help me create, either
- a function to open a list of files (i can create a list based on the variables) and update the data on Open Workbook.
- other function that allows dynamic path reference on closed files.

Or use anyother way around.

I'm open to use VBA if needed.

Thank you!

Guilherme

 

[sandy666]

maybe try Power Query add-in for Excel 2010 and use From Folder option. It will read all files from main folder and subfolders. You can get data from closed files.
This is suggestion only

I am not sure PQ will read an .ods files

 

[voodoo1]

maybe try Power Query add-in for Excel 2010 and use From Folder option. It will read all files from main folder and subfolders. You can get data from closed files.
This is suggestion only

I am not sure PQ will read an .ods files

Thanks for the reply, I gave it a try. Allthough it seems I would have to redo again as the year changes.
Also when choosing a folder, it comes with alot other files, that I dont need the data and I cant choose from specific files names or something like that.
It's not clear how to "copy" the data as it is in the original sheet, from one file to another.
Struggling :P

 

[sandy666]

you can filter unwanted files (unselect all then only eg. xlsx only) and then use the Append option (you said all files have the same structure)
also you can add/remove additional files then refresh end result to see new data.
The best option is work with Tables, not with noname ranges.

 

[sandy666]

For more info How To:

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[voodoo1]

I'm looking for this guide:

Erlandsen Data Consulting

Erlandsen Data Consulting, Excel & VBA tips.

www.erlandsendata.no

» Import data from a closed workbook (ADO)

Anyone knows how this code works, and how I could apply it to my case? i.e., referring to different path/folders?

Thanks for helping!