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:
- Transfer Type: Your choices here are Export or Import. In this case you’ll choose Import.
- Spreadsheet Type: This is essentially telling Access the file type being imported. In this case, Excel.
- 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.
- File Name: This is the path of the source file to be imported.
- Has Field Names: If Yes, Access will not import the first row of data into the table.
- 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.