Pulling data from multiple workbooks

Question

I am trying to find the best and easiest way to pull data from various workbooks without slowing down the consolidated file much. I currently use external links but that makes the consolidated file extremely slow and it isn't easy to roll all the files forward on a monthly/quarterly basis. Step by step instructions will be very helpful as well.

Answer 1

Thank you both so much, Nothing Left to Lose and Jeovany CV,

I will take a look at the instructions you both sent and hope I can follow. Here're a little more details that I should have mentioned - The data I am trying to pull are located on the same worksheet and same column/cells in each of the two dozens or so source files/workbooks, the one destination file lists the descriptions in the same order (say in column A) with each source file data in subsequent separate column (e.g. source file 1 data in column C, source file 2 data in column D, source file 3 data in column E... etc.). I don't know whether this makes a difference to your suggestions, just fyi.

Again really appreciate the advice.

Answer 2

Hi Parker

According to the described scenario,

IMHO,

Power Query might give you the expected results in a very dynamic way with a lot of flexibility.

Re, "... The files are not in the same folder and there're about two dozen source files."

In a workbook, You may create as many queries as you need from each folder containing the group of excel files

The videos below will show you how to do it.

https://youtu.be/fHFUh6EhBcw

https://www.youtube.com/watch?v=0NX-GctfZuU&t=445s

Re, "... Due to the volume and sizes of these files (each over 1GB) they will be closed, otherwise they'll definitely constantly crash my computer if kept all open."

Import files Into Data Model

https://youtu.be/LSjrrDRF0q0

I hope this helps you and leads to a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 3

Re: "willing to try"

The VBA code has to know where the files are located and how to identify them.

It is possible to search file manager using code but that gobbles time and resources.

Creating a new folder and placing copies of the source files in the new folder simplifies things. Your specify the path of the new folder and the code extracts the data from every file in the new folder...
Right-click any existing folder and chose New | Folder

Right -click any file and while holding down the right-click button drag the

file to the new folder.

When you let up on the right-click button, you are presented with options to:

Move here, Copy Here and Cancel.

The code opens each file and copies the data and places it in the consolidated file.

It then closes the file and moves on to the next file.
The is done invisibly until the code completes.

So, if you are still interested, needed are...

The complete file path to the folder.

The name of each source file.

The exact location in each source file of the data to copy: sheet names and

cell address(s) of the data.

The name of the consolidation file and the locations where the copied data

is to be added. The consolidation file should be open while the code runs.

Note: the above will not work if any of the files are shared.

      Ask your boss or your IT guy about using VBA  code 

     (Visual Basic for Applications) on your computer to speed up processing.

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

Answer 4

Thank you. The files are not in the same folder and there're about two dozen source files. Due to the volume and sizes of these files (each over 1GB) they will be closed, otherwise they'll definitely constantly crash my computer if kept all open. And yes the data locations in the other files are always on the same sheets and cells.

I do not know whether I am allowed to use VBA code...but I'm willing to try and learn anything that'll make this recurring work easier and more efficiently.

Answer 5

Re: get data from other files

Some questions...

Are the other files all in the same folder?

Approximately, how many other files are there?

Are the other files open when the consolidated file is open?

Are the data locations in the other files always in the same sheets and cells?

Do you have Power Query? Do you know how to use it?

Are you allowed to use VBA code? Are you willing to try it?

'---
Nothing Left toLose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU