cassyerin
asked on
Exporting from Access to Excel (Docmd.Transferspreadsheet) in a live window
I have employed the code for the docmd.transferspreadsheet (thanks Nico).
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMASTER_Temp", "C:\ListExport.xls"
However, instead of exporting and saving to the C:\ListExport.xls, I would like to export to a live window (I know how to open a live window in excel)
Dim oApp As Object
Set oApp = CreateObject("Excel.Applic
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
Any suggestions on how to export in the window?
Thanks!
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMASTER_Temp", "C:\ListExport.xls"
However, instead of exporting and saving to the C:\ListExport.xls, I would like to export to a live window (I know how to open a live window in excel)
Dim oApp As Object
Set oApp = CreateObject("Excel.Applic
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
Any suggestions on how to export in the window?
Thanks!
I use copyfromrecordset for all spreadsheet creations for end users because it shows the work as it occurs...
ASKER
Do you know where I'm going wrong?
---
Dim objXLWb As Object
Dim objXLSheet As Object
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(st
'Start Excel
Set objXLApp = CreateObject("Excel.Applic
objXLApp.Visible = True
'Open Worksheet & Activate
Set objXLWb = objXLApp.Workbooks.Add
Set objXLSheet = objXLWb.ActiveSheet
'CopyRecordset
objXLSheet.CopyFromRecords
objXLSheet.Columns.AutoFit
---
Dim objXLWb As Object
Dim objXLSheet As Object
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(st
'Start Excel
Set objXLApp = CreateObject("Excel.Applic
objXLApp.Visible = True
'Open Worksheet & Activate
Set objXLWb = objXLApp.Workbooks.Add
Set objXLSheet = objXLWb.ActiveSheet
'CopyRecordset
objXLSheet.CopyFromRecords
objXLSheet.Columns.AutoFit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can get it to run using the docmd. option, however, I want it to export into excel so that data manipulation can be made and the user can choose where to save it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
Split: jadedata {http:#10671939}, Jeremyw {http:#10671818}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
stevbe
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Split: jadedata {http:#10671939}, Jeremyw {http:#10671818}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
stevbe
EE Cleanup Volunteer
That command is in the category of a menu command and will not function in ways that say "CopyFromRecordset" would.
With CopyFromRecordset you would set up the records in a dbdynaset type queue and pass them to an excel worksheet via CopyFromRecordset
regards
jack