Already a member? Log in

Sign up with your...

or

Sign Up with your email address

Add Tags

Duplicate Tags

Rename Tags

Share This URL With Others!

Save Link

Sign in

Sign Up with your email address

Sign up

By clicking the button, you agree to the Terms & Conditions.

Forgot Password?

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.

URL: http://datapigtechnologies.com/blog/index.php/using-access-to-combine-multiple-excel-files-method-1/

Using Access to Combine Multiple Excel Files: Method 1 » Bacon Bits:

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.

Share It With Others!

Comments

Using Access to Combine Multiple Excel Files: Method 1 » Bacon Bits: