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.
peter57r
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.
ASKER
peter57r,
I wanted the process to run itself automatically through a VB script. Is this possible.
I wanted the process to run itself automatically through a VB script. Is this possible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\MyDatabas
sSql = "Select * from dbo_Storedata"
Rs.Open sSql, oConn
activesheet.Range("A1").Co
oConn.Close
HTH
Cal
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\MyDatabas
sSql = "Select * from dbo_Storedata"
Rs.Open sSql, oConn
activesheet.Range("A1").Co
oConn.Close
HTH
Cal
PS-Don't forget to update the Database name. You select the table in the SQL statement.
Cal
Cal
ASKER
Cbrine:
Where are you referencing the name/location of the Excel Spreadsheet in your code?
Where are you referencing the name/location of the Excel Spreadsheet in your code?
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").Co
to
sheets("Sheet1").range("A1
Where "Sheet1" is your sheet name.
HTH
Cal
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").Co
to
sheets("Sheet1").range("A1
Where "Sheet1" is your sheet name.
HTH
Cal