Please enter your username below and press the send button.A password reset link will be sent to you.
If you are unable to access the email address originally associated with your Delicious account, we recommend creating a new account.
Recently Saved by kpraveenkumars on December 21, 2011
First saved by andy_27 on August 28, 2009
Ahh, the classic story. Boy gets job. Boy becomes the department data collector. Boy meets 25 Admins who send him a spreadsheet every week. Boy copies and pastes into one workbook. Boy lights himself on fire.
Combining multiple Excel worksheets into one data table is such a common task, I would bet a pound of bacon that most of us have been through that ordeal once or twice.
The good news is that you can just scour the internet, and you’ll probably find dozens of techniques for combining multiple Excel files together.
Here’s one simple way you can leverage Access to combine multiple Excel files (without VBA).
Step 1: Create links to your Excel workbooks in Access.
You’ll have to repeat this process for each excel workbook you until you have them all linked in your database.
As you can see, the Excel linked tables are easy to spot.
Step 2: Start a new query in SQL View
Step 3: In SQL View, start typing Select statements for all the linked Excel tables, using the Union operator between each select statement.
Step 4: Save and Run the query.
The result will be a single dataset containing the combined data from all the linked files.
As long as the linked Excel files are in the same location, Access will automatically refresh the links for data changes each time you open the database. This means you can run through this setup once, then simply run the union query each time you need to re-combine the your Excel files.
Next week, I’ll cover another method of using Access to combine Excel workbooks.
Update:Rob correctly points out that I negelected to mention a key rule about the UNION operator.
The UNION operater will, by default, remove any duplicate rows you may have in your data.