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

 

[Ron de Bruin]

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

 

[mp80237]

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

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

--

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

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.

 

[Ron de Bruin]

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

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

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

--

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

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.

 

[mp80237]

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

Working Ok here in 2007

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

My issue is with Excel 2007 and Access 2007, the examples provide are
previous versions.  Also, my queries have already been built in Accessas
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.  Thankyou
for the help.

M

Maybe this page that use ADO will help
http://www.rondebruin.nl/accessexcel.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
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.- Hide quoted text -

- Show quoted text -

I tried you ideas several ways. When I left the files as .mdb
and .xls the macros ran fine.
When I save the files as 2007 format (Excel with extension .xlsm and
Access with extension .accdb) I get error "Error copying data" This
is the problem I am running into. I need to save the files as a
Access 2007 version, not 2000-003 version.

M

 

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