VB Macro to read data from an Access table into an Excel spreasheet

I am able to link an access table to an Excel Spreasheet of text file which brings the data in dynamically.  Is there a way to bring data into an Excel spreadsheet from an Access Table thas has been modfied. I would like to do it via a VB macro if possible.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
vincem1099Commented:
You will need to create the data link as peter57r described and then you could use some vb script similar to what follows.  This scrip will open up excel and refresh the query that you created in the first step.  Save with a .vbs extension and it can be scheduled through Task Manager.

set oExcel = createobject("Excel.Application")
set oWorkbook = oExcel.workbooks.open("filename.xls")
Set oSheet = oWorkbook.Worksheets("sheetname")
oExcel.visible=True
'Refresh Report data from Access
For Each oQueryTable In oSheet.QueryTables
    oQueryTable.Refresh BackgroundQuery:=False
 Next
'Close Excel Program
oExcel.quit
0
 
peter57rCommented:
Use the Data>ImportExternalData option to import from the Access table.  If you use the default settings you will be able to refresh the data from the Data menu at any time.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
peter57r,

I wanted the process to run itself automatically through a VB script.  Is this possible.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Calvin BrineCommented:
I believe that vincem1099's code works in Access and exports to Excel.  You want code that works in Excel and extracts data from Access.  Give this a try.

Private Sub CommandButton1_Click()
Dim oConn As ADODB.Connection
Dim sSql As String
Dim Rs As ADODB.Recordset

Set oConn = New ADODB.Connection
Set Rs = New ADODB.Recordset

oConn.Open _
        "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=C:\MyFolder\MyDatabase.mdb"
sSql = "Select * from dbo_Storedata"
Rs.Open sSql, oConn
activesheet.Range("A1").CopyFromRecordset Rs
oConn.Close


HTH
Cal
0
 
Calvin BrineCommented:
PS-Don't forget to update the Database name.  You select the table in the SQL statement.

Cal
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Cbrine:

Where are you referencing the name/location of the Excel Spreadsheet in your code?
0
 
Calvin BrineCommented:
morinia,
  The code doesn't reference the spreadsheet, other then pasting the ADO recordset to the activesheet.  If you want the data to go to a sheet other then the activesheet just do this.

activesheet.Range("A1").CopyFromRecordset Rs
to
sheets("Sheet1").range("A1").copyfromrecordset Rs

Where "Sheet1" is your sheet name.
HTH
Cal
0
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.

All Courses

From novice to tech pro — start learning today.