Using Access to Combine Multiple Excel Files: Method 2

July 27th, 2009 by datapig Leave a reply »

In my Pulitzer worthy post, Using Access to Combine Multiple Excel Files: Method 1, I showed you a simple way to leverage linked objects in Access to combine multiple Excel files.

This weekend, while checking out some of the newsgroups, I ran across a question from Ashish who asks:

"How can I specify the row number from where the data consolidation should start? I.e. I want to somehow exclude the rows (at the top of the 3 sheets) which I do not want to consolidate."

In other words, Ashish needs to specify a particular range from which to pull and consolidate the data.

Method 1  basically requires a clean dataset with nothing else on the worksheet.  There may be situations, however, where the data you are trying to consolidate lives on heavily formatted worksheets where other meta data makes it impossible to use a simple linked object.

 

In this example, I have several workbooks that I need to consolidate into a single table of data. But as you can see, my data doesn't start on row 1. Plus there are other formatting elements in this workbook that prevents me from simply linking to it from Access.

 

The answer is to us the TransferSpreadsheet macro action.

 

The first step is to create a new Access macro.  In Access, macros aren't recorded as they are Excel.  Instead, you create a new macro object, then define what it needs to do by giving it 'Actions'.  

You can create a new macro in Access 2007 by going to the Create tab  and selecting Macro in the Module dropdown.

In Access 2003, you simply click 'New' while in the 'Macros' window.

 

Note that In Access 2007, Microsoft decided to protect you from 'dangerous' macro actions by hiding some of the actions available to you  (you'll be glad to know that they hide only the useful actions).

So be sure to click 'Show All Actions'.  If you don't, you won't be able to get to the Transfer Spreadsheet action.

 

 

Starting a new macro will activate the 'Macro Grid".

In the Macro grid, you can tell Access to perform a particular 'Action'. In this case, you'll select the TransferSpreadsheet action.

Each action has its own arguments which will appear below the Macro Grid.  The TransferSpreadsheet action has six arguments: 

  1. Transfer Type: Your choices here are Export or Import. In this case you'll choose Import.
  2. Spreadsheet Type: This is essentially telling Access the file type being imported. In this case, Excel.
  3. Table Name: Here, you will specify the name of the table where the data will be imported. If the table doesn't exist, it will be created. If the table exists, the imported data will be appended to the table.
  4. File Name: This is the path of the source file to be imported.
  5. Has Field Names: If Yes, Access will not import the first row of data into the table.
  6. Range: Here, you can tell Access exactly which range to source for the data. In this case the range is Market!A2:I362

  

Once you configure your TransferSpreadsheet action, you can add as many as you need.

In this example, I'm importing data from eight different workbooks. So I've created a separate TransferSpreadsheet action for each workbook.

When I run this macro, Access will go through each action, importing my data as specified in the arguments.

 

 

What if your data is dynamic?

If your data is dynamic and will expand or shrink every time you import, you have two options.

 

The cheap option is to define a range that is large enough to account for any growth. In this example, I've changed my range to capture Market!A2:I5000.

The idea here is to define a range that probably won't be exceeded.

The drawback is that I'll have to account and managed any blank rows that are imported.

 

 

The more stable option is to leverage Excel's Table/List functionality to automatically expand or shrink the range as needed.

 

Step 1: Convert your dataset into a Table or List.

In Excel 2007, you convert a dataset in to a defined 'Table' by choosing Table under the Insert tab.

In Excel 2003, this functionality is actually called a 'List'. Click Data -> List -> Create List.

 

 

Define the range that makes up your table.

 

 

Step 2: Create a Named Range using your Table/List

Once you have you've converted your dataset in a Table or List, create a Named Range from the same range that is encapsulated by your Table/List.

 

Step 3: Change the Range argument to point to your Named Range.

Using the Named Range allows Access to get the benefit of the dynamic nature of the Table/List object. This means that if the Table/List shrinks or grows, Access will automatically capture that change via the Named Range.

The obvious drawback to this option is that you'll have to repeat these steps for each workbook that needs to be consolidated.  Although it could be a one-time task, it may not be feasible in all situations.

 

Note that Excel 2007 auto-generates a Named Range for any Table you create. In this example, a NamedRange called Table2 is has been created.  However, Access doesn't seem to recognize these Named Ranges. Using Table2 in my macro invoked an error.  So it would seem as though you'll have to create your own Named Range as specified in Step 2 - even though Excel creates one automatically.

Advertisement

16 comments

  1. Hi,

    Thank you for sharing this workaround. Just wanted to add that if one wants dynamic updation, then one would have to select “Link” in the transfer type of the Access macro instead of “Import”

  2. DataPig says:

    Ashish: Good tip.

  3. Preet says:

    once you run a query, is there a way to change the name of the field?

  4. DataPig says:

    Preet: In the query grid, you can assign an ‘Alias’ to any field. For exampe if you have field called SALES, you can change that field name in your query results by giving it an alias of Revenue.

    In the Query Designer, simply type Revenue just before the name of the SALES column, placing a colon between Revenue and SALES.

    Revenue: SALES

  5. Preet says:

    thanks! worked

  6. rodrigo miron says:

    Just came across your blog today and just spent an hour here!
    I asked this at stackoverflow, check it out: http://stackoverflow.com/questions/1180430/data-extraction-with-excel

    Great blog!!!

  7. Hi,

    As suggested above, when you convert the range to a table in Excel 2007, any data added to the source sheets gets added to the consolidated sheet (which is exactly what we wanted). However, if you add date by columns, it does not work i.e. the new columsn added do not appear in the consolidated sheet.

    There are 2 ways to workarounf it – manually edit the range of each name to include the spare column. The other is to initially include 2-3 spare columns in the range definition.

  8. DataPig says:

    Ashish: Interesting. I haven’t run into that issue before. Thanks for the heads up.

  9. judgepax says:

    YOU ARE MY HERO!! I use the “RunSQL” Macro Action a lot but kept getting a completely non-helpful “The text you entered isn’t an item in the list.” error message. And of course, Microsoft Help was no help when I tried to find out where RunSQL was moved, renamed, or whatever. Only way I could figure out a way around this was to copy and paste a RunSQL row from a pre-Access 2007 Macro. Is there any way to have “Show All Actions” be the default? (Of course Microsoft Help, can’t help with this either.)

  10. King Granite says:

    I am using a Dynamic Named Range and the Transfer macro is unable to “find the range” in the worksheet.

    The range is constructed using the OFFSET/COUNTA trick which saves me from converting the data into a table (which is not an option for me).

    Any ideas?

  11. datapig says:

    King: I vaguely remember that dynamic ranges are not recognized. Fortunately, the Transfer function automatically makes the range dynamic.

    Try this: Make a regular named range consisting of only the column headers. When you transfer data to that range, the named range will automatically grow and shrink as needed. Kinda cool, because you don’t have to worry about managing dynamic ranges.

  12. King Granite says:

    I tried what you asked, DP, but it did not work. It only linked the headers that were specified in the range.

  13. Zigen says:

    Is there a way to combine multiple tabs under one worksheet?

  14. Karen says:

    Hi
    I need to simplify my process – I have about 20 support centres who send 10 sheets per month to head office. I have linked these (lots of linking!) and created a union query to combine them.
    Then I delete the previous contents of my combined table: DELETE UnionTbl.* FROM UnionTbl, and use: INSERT INTO UnionTbl SELECT UnionQryName.* FROM UnionQryName to copy in the most updated Union query.
    BUT: I have about 200 linked sheets plus 10 UnionQueries which I find cumbersome. Is there an easier way of doing this?
    Thanks, Karen

  15. NavtejS says:

    Very True, Great

  16. Nate says:

    Is there a macro to update all to all the files you want to include? To eliminate the need to have a manual input for a new daily file added to the list to import.

Leave a Reply

Powered by sweet Captcha