Importing Data to Excel 2007 from Access 2007
 

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. 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.

Importing Data to Excel 2007 from Access 2007
 

Maybe this page that use ADO will help
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mp80237" wrote in message ...
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. 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.

Importing Data to Excel 2007 from Access 2007
 

My issue is with Excel 2007 and Access 2007, the examples provide are
previous versions. Also, my queries have already been built in Access as
crosstab queries, and I don't want to import as a pivot. Don't want to go
into details as why, but want to keep existing query in access. Thank you
for the help.

M

"Ron de Bruin" wrote:

Maybe this page that use ADO will help
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mp80237" wrote in message ...
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. 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.

Importing Data to Excel 2007 from Access 2007
 

My issue is with Excel 2007 and Access 2007, the examples provide are
previous versions.

Working Ok here in 2007



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mp80237" wrote in message ...
My issue is with Excel 2007 and Access 2007, the examples provide are
previous versions. Also, my queries have already been built in Access as
crosstab queries, and I don't want to import as a pivot. Don't want to go
into details as why, but want to keep existing query in access. Thank you
for the help.

M

"Ron de Bruin" wrote:

Maybe this page that use ADO will help
http://www.rondebruin.nl/accessexcel.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mp80237" wrote in message ...
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. 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.

Importing Data to Excel 2007 from Access 2007
 

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

"mp80237" wrote:

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. 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.