From Excel 2010 using VBA: Import Access table/query into named range on existing worksheet
Posted on 2014-10-26
For some reason, I thought this was going to be easy....
This is all in Office 2010.
I am working on a project in which the principal objects are:
1. An Excel workbook (.xlsm) which is the front-end user interface.
2. An Excel add-In (,xlam) which controls all of the processing.
3. An Access database (.accdb) which holds the working data.
I need to be able to exchange data between the Excel workbook and the Access database. This exchange of data is run from the Excel Add-In.
I didn't have much trouble setting up a procedure to export data from named ranges in the workbook to the database. But setting up a procedure to import data from the database into a named range on an existing worksheet is eluding me for the moment. I have tried a couple of the connection methods available. My thoughts were if I could get a good running connection, I could then modify the SQL in the connection object for each different send. I'd still like to set this up with a connection object if possible, but first I need to get something that simply works.
It appears my problem is in identifying my target destination (i.e. the named range in the existing workbook).
This is a truly massive workbook, and I need to find an efficient way to do this. Can anyone offer me a suggestion on how best to approach this?