Access Table - import into Excel Tab

D

Dolemite44149

New Member
Joined
Aug 22, 2008
Messages
8
I've read on the board multiple threads that explain the code needed to import a MS Access table into an excel spreadsheet. I continue to struggle!

Mine is a very simple task - just copy the access table and overlay all the info in the excel tab. My excel macros will take care of the rest. Can someone help me out and post the VBA code for this? Thank you in advance.

1. The access dbase will not be open
2. Access dBase = c:\temp\tracking.mdb
3. Table in access = Table1
4. Tab in excel (will be open and will launch the routine) = dBase

Thanks again!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
If you're actually importing, here is one way - and yes it is a macro. You need to include a reference to Microsoft ActiveX Data Objects 2.x Library.

Code: 
Public Sub ImportData()
  Const strDb As String = "C:\Database2.mdb"
  Const strQry As String = "SELECT * FROM Table1"
 
  Dim rs As ADODB.Recordset
  Dim cn As ADODB.Connection
 
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
 
  Set rs = New ADODB.Recordset
 
  With rs
    Set .ActiveConnection = cn
    .Open strQry
  End With
 
  Sheet1.Range("A1").CopyFromRecordset rs
 
  rs.Close
  cn.Close
End Sub

Without a macro, you can create a data connection (Data -> External Data -> New Database Query) and place it on the sheet, that way you can refresh it, and no macro required.
 
Upvote 0
And using your example...

Code: 
Public Sub ImportData()
  Const strDb As String = "C:\temp\tracking.mdb"
  Const strQry As String = "SELECT * FROM Table1"
 
  Dim rs As ADODB.Recordset
  Dim cn As ADODB.Connection
 
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
 
  Set rs = New ADODB.Recordset
 
  With rs
    Set .ActiveConnection = cn
    .Open strQry
  End With
 
  ThisWorkbook.Worksheets("dBase").Range("A1").CopyFromRecordset rs
 
  rs.Close
  cn.Close
End Sub
 
Upvote 0
Thank you both - it worked PERFECTLY!! Not sure why I struggled on this...

One other quickie. I have this code setup in a Public Sub now. For the other macros - I would like for them to run the Public Sub ImportData() and then continue on with their code. Im stuck on the sytanx. (almost like inserting a GoSub and return..) I dont want to list the same code in each module.

How do I do that? Thanks again!

Greg
 
Upvote 0
Greg,

I don't fully understand your latest question. Is it just having a VBA code line "ImportData"? Or, "Call ImportData"?

FYI, for the actual code doing the work, some slight variations. Just to give you some other ideas/options.

One, using only the recordset object. Late bound.
Code: 
'With a recordset object
Sub late_bound_recordset()
 
  Const strDb As String = "D:\temp\tracking.mdb"
  Const strQry As String = "SELECT * FROM Table1"
 
  Dim rs As Object
 
  Set rs = CreateObject("ADODB.Recordset")
 
  rs.Open strQry, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
  ThisWorkbook.Worksheets("dBase").Range("A1").CopyFromRecordset rs
  Set rs = Nothing
End Sub

And with just the connection object, again late bound.
Code: 
'With a connection object
Sub late_bound_connection()
 
  Const strDb As String = "D:\temp\tracking.mdb"
  Const strQry As String = "SELECT * FROM Table1"
 
  Dim conn As Object
 
  Set conn = CreateObject("ADODB.Connection")
 
  With conn
    .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
    ThisWorkbook.Worksheets("dBase").Range("A1").CopyFromRecordset .Execute(strQry)
    .Close
  End With
  Set conn = Nothing
End Sub
HTH, Fazza
 
Upvote 0
PS

NB in my code the path is D drive, not C drive from previous posts. Be sure to change that if testing.

F
 
Upvote 0
Sorry Fazza. My response was very vague.

Pulling the info from access works great. How do i take the code that pulls in the access table (Public Sub ImportData) and run this routine as the first step of every other macro i have written? I have 2 options.

1) Copy and paste the VBA (from Public Sub ImportData) into all other modules multiple times (not the end of the world but there has to be a simplier way)

2) Reference / insert one line (ie like a GoSub w/ a return) to run the Public Sub ImportData module and then come back to the rest of the other code.

Thanks again!
 
Upvote 0
OK. Then like I wrote above. So, not option 1 in your post (as you'd expect). But per option 2. The calling line can be just "ImportData". Or "Call ImportData". Or maybe even "Call ImportData()".

For this the "ImportData" code should be in its own code module. OK? So, in the VBE you can see modules listed (on the LHS) when you go view projects (CTRL-R). There is one for the workbook and one for each worksheet. You can insert a code module and put your subroutine code there. If that doesn't make sense please post again I'm sure I can find some good online info to explain it - I'm sure Chip Pearson has info on it. Also VBA help would too.

regards, Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Edit same table form both Excel and Access
Replies
1
Views
392
lcorcoran
L
S
  • Question
Faults tracking system - VBA & Excel?
Replies
2
Views
929
si3po
S
D
  • Question
VBA - Import Workbook/Choose Sheet/Display Details
2
Replies
10
Views
2K
Trebor76
T
A
  • Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
P
  • Question
Import Excel data to an MS Access Table too slow!
Replies
3
Views
3K
Joe4
Joe4

Forum statistics

Threads
1,214,971
Messages
6,122,517
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top