Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-10-10
7
Medium Priority
?
917 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:morinia
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20049789
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
 

Author Comment

by:morinia
ID: 20050107
peter57r,

I wanted the process to run itself automatically through a VB script.  Is this possible.
0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 375 total points
ID: 20050384
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Expert Comment

by:Calvin Brine
ID: 20050681
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
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 20050691
PS-Don't forget to update the Database name.  You select the table in the SQL statement.

Cal
0
 

Author Comment

by:morinia
ID: 20052754
Cbrine:

Where are you referencing the name/location of the Excel Spreadsheet in your code?
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 20057296
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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