Solved

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

Posted on 2014-10-26
4
1,034 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now