asked on

From Excel 2010 using VBA: Import Access table/query into named range on existing worksheet

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?
Thank you,
Todd

Professor J

this code below can be helpful for a start up

Public Sub Export()

'export data from Excel to Access by issuing SQL Insert command

Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\DataBS.mdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
dsh = dsh = "[" & Application.ActiveSheet.Name & "$]" & "YOURNAMEDRANGE"
cn.Open scn
ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh



cn.Execute ssql


End Sub

Open in new window

shambalad

ASKER

Looks good. I'll get back to you in a few minutes. Thanks
Todd

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shambalad

ASKER

Thank you aikmark. Sorry I didn't get back to you sooner; I've been remotely connected to my client site for the last many hours, so I haven't been aware of any messages coming into my mailbox. As it turns out, your solution is the one I finally arrived at. As such, you get the points. I appreciate your help. This is a large 6-12 month project that they're trying to push out before the end of the year. I'm sure I'll be back with more questions.
Thanks again,
Todd