DOC Office Consulting

How can I import data from multiple source workbooks into separate worksheets in a single file?

Although this can be achieved with links to the source file – links can easily be broken and can also make the file slow and cumbersome. So instead of creating links, I like to create processes that import the data into a specified worksheet. There’s a few ways to do this, but copying and pasting is by far the easiest to understand for those new to programming, so I’ve created 2 macros in the attached that use copy and paste to help get you on your way.
These 2 macros both copy and paste data from the source files into separate worksheets in the destination file but work quite differently

ImportSpecifiedColumns

This macro is very user friendly, for the button clicker, not the programmer and handles errors well. Here’s how it works:
Using the list of files and associated worksheets on the worksheet named Home, the process:

• Loops through each file and corresponding worksheet listed on the Home tab
• It checks that the file exists and allows the user the opportunity to choose a new file if the one listed isn’t accessible or doesn’t exist
• It checks that the specified worksheet exists and advises the user if it doesn’t.
• If both tests are passed, the process checks to see if the files listed are already open, if not it opens them
• Once the files are opened, the process loops through each heading in the listed worksheets and finds the matching column heading in the source file





• If any of the column headings are missing, the process will advise the user
• Once all matching column headings are found, the process copies the data below the header from each matching column into the appropriate column of the associated worksheet

This process utilises separate functions to check whether the listed files exist and are accessible, whether the listed files are open, whether the specified worksheets exist and whether the column headings in the worksheets exist in the source files. It also uses multi-dimensional array variables to allow a variable number of files with a variable number of columns to be imported.
While it is complicated, it is very, very cool.

ImportWholeSheets

This one is much easier to understand the code, but not nearly as user friendly if the data isn’t correct. The process simply loops through a list of files and worksheets that have been hard coded and copies the entire worksheet from the source file into the worksheet of the current file

The number of worksheets and associated files aren’t dynamic, and they can’t be easily updated by the user – they have to be edited in the code.
However, once the workbook and worksheet variables are specified, copying the whole worksheets is a very simple matter:

Once that’s done, the workbooks are closed

Download the workbook and have a look at the macros. Probably start with ImportWholeSheets macro – although it’s dirtier, it’s much simpler to understand.
Click here to download a the file
Once you’ve had a look at them, you’ll want to modify them to suit your purpose.

• Note to work with ImportWholeSheets, you’ll need to edit the files specified in the code
• to work with ImportSpecifiedColumns you’ll need to edit the column headings in the spreadsheets.

Don’t be scared, step through the modules one line at a time and try to understand how they work.

Good Luck 🙂
As always; if you have a question on a specific Excel topic, I’m here to help. Don’t hesitate to get in touch.
Cheers,
Dani