Access 2007 multiple queries to specific Excel Worksheet/cells

[Rob Bartle]

Ok, I'm definitely a noob when it comes to vba code so bear with me. What I'm attempting to do is update an Excel 2007 workbook with several sheets. A few sheets are used to compile a pivot table.

In Access I have 3 tables and 2 queries that need to update specific cells within those workbooks. For example, the qExample1 query from Access is going to update Excel Sheet1 entering from cell B5 and down to however many rows the query returns. The second tExample1 (table) will update Excel Sheet2 entering from cell B5 and down. The others are the same. To simplify I want them all entering each sheet at cell B5.

I've tried to go the other way, from Excel opening connections to the Access DB but I get reference errors that I can't seem to find (and would rather not pursue).

Any help would be appreciated.

[NeoPa]

Rob Bartle:
I've tried to go the other way, from Excel opening connections to the Access DB but I get reference errors that I can't seem to find (and would rather not pursue).

I have a strong suspicion you'll be changing your mind on this shortly ;-)

It is possible (though somewhat painful) to create or overwrite data from an Access Export into a specific worksheet of an Excel workbook. This leaves you still with a reuirement to manipulate that data as you've specified where you want the data to go. That gets a little messy and requires Application Automation. Not for the feint-hearted, though not rocket-science either.

On the other hand, importing from Access into Excel can be done using MS Query and, assuming you can successfully design the query ok, can provide exactly the functionality you need. I suppose it's not that straightforward, but as long as you're methodical and get the details right it will work for you.

You should be aware that the SQL flavour used in MS Query is somewhat different from Jet SQL (as used in Access) so if you decide to amend what's created for you automatically then do it with care, replacing only like with like where possible.