Getting external data from Dynamic Path and Closed Files

voodoo1

New Member
Joined
Dec 14, 2019
Messages
3
Office Version
2010
Platform
Windows
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
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
 
Last edited:

voodoo1

New Member
Joined
Dec 14, 2019
Messages
3
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,140
Messages
5,484,972
Members
407,476
Latest member
PDT Inc

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top