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
LVL 7
shambaladAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
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

0
shambaladAuthor Commented:
Looks good. I'll get back to you in a few minutes. Thanks
Todd
0
aikimarkCommented:
Will your named range be the same size as the data you are importing from Access?  If not, you need to clear the contents of the named range before you do your import.

The overview of importing Access data.
1. instantiate an ADODB connection, pointing to your Access database
2. Open an ADODB recordset using the connection from #1
3. instantiate an Excel range variable and point it to the named range or Cells(1,1) of the named range.
4. Invoke the range variable's CopyFromRecordset method using the recordset variable (from #2) as the parameter.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shambaladAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.