Today, I ‘d like to show you a nifty trick that will allow you to send Variable length tables from Access to a specific Excel Range without code.
That’s right, I said without any VBA coding. I know you’re as excited as I am, so here we go.
The Proposal:
I have a model where my table feeds a chart. Right now, the data in this table is hard coded and static. However, the data actually comes from an Access Query.
My proposal is to have an Access application that sends data to Excel dynamically. This way, I can have my Access application output an Excel report. That being said, I’d like to have Access be able to automatically send the data to the correct range.
Step 1: Create a Named Range
The first step is to create a named range where I can point Access to. In this example, I’m going to specify my column headers as the named range. No, I’m not crazy. This range will automatically shrink and grow with data, so there is no need to specify any more than the headers. Note that I called my range ChartData.
Step 2: Step up the Query
In this example, I’m using a query that gives me the sum of revenue by period. You can use a table if you’d like. The idea is that you set up an object that holds the data to be transferred. In this example, my query is called ‘SumByPeriod’.
Step 3: Step up the Transfer Macro
Create a new macro that will use the TranferSpreadsheet action to transfer the data from the specified query to the named range.
Here, I’m telling Access to export data from the SumByPeriod query to the ChartData range.
Note that you’ll have to explicitly define the location of the workbook that holds the named range. This will work wether the workbook is open or closed.
The Result
Once you save your macro, you can run it. After running my macro, you’ll see that the data was sent to Excel exactly where I wanted it. Notice that he range has automatically expanded to fit the data. 
If I add more data to the query and run the macro again, my ChartData range will expand to fit the added data.
Here’s the nifty bit. If I reduce the amount of data going out, my ChartData range will contract to fit only the transferred data.
Cool trick.
Is there any reason why you would use this approach over say defining a workbook connection within Excel to pull the data into your table or pivottable, rather than pushing it in from Access?
I guess in your example, one bonus is that the worksheet doesn’t have to be open in order to refresh. But on the down side , you can’t move or send the workbook to someone else given you’ll have to explicitly redefine the location of the workbook that holds the named range.
I am with Jeff. I would build the query in Excel to pull data from Access. Some VBA Code that lets the user to specify the path of the Database in a cell and refresh the query based on the path….then the Database location can change and the query would still work
Neat trick Mike, is it all versions of access that support this, I’ve not seen it before.
Jeff: I agree with you.
I didn’t make it clear before, but I was writing from an Access standpoint (I’ve added text to the post since your comment).
My proposal is to have an Access application that sends data to Excel dynamically. This way, I can have my Access application output an Excel report. That being said, I’d like to have Access be able to automatically send the data to the correct range.
Ross: This definately works in Access 2003 and 2007.
I no longer have Access 2000, so I couldn’t say for sure if this works in that version.
I am looking for examples of code to do the oppsite. I use Excel to update daily information. I would like to be able to archive he data to an access file at the end of the day. Do you have any examples on your site that covers this topic? Excel to Access 2007