Solved

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

Posted on 2014-10-26
4
1,080 Views
Last Modified: 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?
Thank you,
Todd
0
Comment
Question by:shambalad
  • 2
4 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40405183
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
 
LVL 7

Author Comment

by:shambalad
ID: 40405207
Looks good. I'll get back to you in a few minutes. Thanks
Todd
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40405568
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
 
LVL 7

Author Comment

by:shambalad
ID: 40405649
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question