Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Access database into Excel 2007 | Excel Discussion (Misc queries) | |||
Importing data from web into Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 Query Wizard problem with Access 2007 extensions | Excel Discussion (Misc queries) | |||
Convert Excel 2007 data forms to Access 2007 ? | Excel Discussion (Misc queries) | |||
Importing Access Query in Excel 2007 | Excel Programming |