Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-26
4
Medium Priority
?
1,237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 26

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 46

Accepted Solution

by:
aikimark earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

722 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