Importing Data to Excel 2007 from Access 2007

[mp80237]

Hello, we currently upgraded to 2007 from 2000. I had a great vb code
that I
loved and seems to no longer work. It even added the field names. I
know I
am to do a ADODB connection. I have already added the requested
reference.
But this is not working. Current VB script is below. It is grabbing
my crosstab queries in Access and pulling them into Excel. I want to
use the queries I created in Access, I don't want to re-create another
query.

My my new database is
SIRS.accdb and when I changed the name it does not work.

Sub IMPORT_ACCESS_INCIDENT()

Dim db As DAO.Database, qry As DAO.QueryDef, rec As DAO.Recordset

Dim ST As String
Dim i As Integer
Dim R As Long
Dim C As Integer


'set path to database
Path = "P:\Service\SIRS.mdb"

'now get data from Access
Set db = DBEngine.Workspaces(0).OpenDatabase(Path) ' Open database

'get data for Total Incidents
Set qry = db.QueryDefs("Q-Incidents")
Set rec = qry.OpenRecordset
Sheets("Summary").[c2].CopyFromRecordset rec

For Counter = 0 To rec.Fields.Count - 1
Worksheets("Summary").[c1].Offset(0, Counter).Value =
rec.Fields(Counter).Name
Worksheets("Summary").[c1].Offset(0, Counter).Font.Bold = True
Next Counter

End Sub

Please help.

 

[mp80237]

Hello,
found the answer. if anyone needs it, here it is. It will also paste in
headers. "Test" is the name of the worksheet inside the Excel workbook.

Sub IMPORT_test4()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer

'Connect to your Access
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=P:\Service\SIRS.accdb;"
cn.Open

'Create your recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM qryAllIncidents;", cn, adOpenKeyset, adLockReadOnly,
adCmdText



Worksheets("test").[a2].CopyFromRecordset rs

'Add to your current workbook and add the field names as column headers
For Counter = 0 To rs.Fields.Count - 1
Worksheets("test").[a1].Offset(0, Counter).Value = rs.Fields(Counter).name
Worksheets("test").[a1].Offset(0, Counter).Font.Bold = True
Next Counter


'Clean up Objects
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Sub IMPORT_test4()

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer

'Connect to your Access
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=C:\Documents and Settings\mycomputer\Desktop\SIRS TTO.accdb;"
cn.Open

'Create your recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM qryAllIncidents;", cn, adOpenKeyset, adLockReadOnly,
adCmdText

'Add to your current workbook and add the field names as column headers
(optional)


Worksheets("test").[a2].CopyFromRecordset rs

For Counter = 0 To rs.Fields.Count - 1
Worksheets("test").[a1].Offset(0, Counter).Value = rs.Fields(Counter).name
Worksheets("test").[a1].Offset(0, Counter).Font.Bold = True
Next Counter


'Clean up ADO Objects
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Hello, we currently upgraded to 2007 from 2000. I had a great vb code
that I
loved and seems to no longer work. It even added the field names. I
know I
am to do a ADODB connection. I have already added the requested
reference.
But this is not working. Current VB script is below. It is grabbing
my crosstab queries in Access and pulling them into Excel. I want to
use the queries I created in Access, I don't want to re-create another
query.

My my new database is
SIRS.accdb and when I changed the name it does not work.

Sub IMPORT_ACCESS_INCIDENT()

Dim db As DAO.Database, qry As DAO.QueryDef, rec As DAO.Recordset

Dim ST As String
Dim i As Integer
Dim R As Long
Dim C As Integer


'set path to database
Path = "P:\Service\SIRS.mdb"

'now get data from Access
Set db = DBEngine.Workspaces(0).OpenDatabase(Path) ' Open database

'get data for Total Incidents
Set qry = db.QueryDefs("Q-Incidents")
Set rec = qry.OpenRecordset
Sheets("Summary").[c2].CopyFromRecordset rec

For Counter = 0 To rec.Fields.Count - 1
Worksheets("Summary").[c1].Offset(0, Counter).Value =
rec.Fields(Counter).Name
Worksheets("Summary").[c1].Offset(0, Counter).Font.Bold = True
Next Counter

End Sub

Please help.