Importing Data to Excel 2007 from Access 2007

M

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

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

Ron de Bruin said:
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 said:
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.
 
R

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


mp80237 said:
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 said:
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 said:
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.
 
M

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



mp80237 said:
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.

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

mp80237 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top