Hi
I have an Access button which runs 3 exports of queries to 3 seperate pages in an existing Excel file. This works fine.
What i wanted to do was have Access send the data from the 3 queries to an Excel template file automatically but it seems its easier for Excel to do this.
So the data is sent from Access to an Excel temp file. Then, in the temp file I have written some code which opens the template file, and copies data from the temp file, to the template file, saves the template file under a new name, and closes the temp file without saving it.
I have added some code to the button in Access to run a macro I have called AutoOpen. It seems the Aut
pen macro wont work direct from Access when Excel is opened (why is nothing straighforward?)
Anyway, the macro runs, but at the very end after the file has been saved, Access comes up with an "440 : Automation Error". Also it doesnt show the Excel file. Even after closing the Access error, clicking on the Excel file does not bring it up but I can close it by right clicking on the task bar and selecting close no problems. Its as if Access has preference.
So i'm not sure if it is the Access code or the Excel code which is causing the problem, im sure its something simple I have missed out, can anyone help?
Thanks
Gary
Access code:
==========
Private Sub Command35_Click()
Dim strReportTemplate As String
Dim strMacro As String
' set location of the XLS temp file
strReportTemplate = "c:\test\temp.xls"
' dump contents of 3 sep queries into sep pages on the Excel Temp file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_provides", strReportTemplate, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_changes", strReportTemplate, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_ceases", strReportTemplate, False
Dim XLApp As Object
' Open the XLS fle, make it visible, get control and run a macro called AutoOpen
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "c:\test\temp.xls"
.Application.Run "AutoOpen"
End With
End Sub
Excel Code
========
Sub AutoOpen()
Dim HomeWorkBook As Variant
Dim IGSFilename As Variant
Dim IGSWorkBook As Variant
Dim Savename As Variant
Dim savedateday, savedatemonth, savedateyear As Integer
' Set IGS Filename and location (this is the name of the template file to copy data into)
IGSFilename = "C:\test\IGS Daily.xls"
Application.ScreenUpdating = False
' set current Temp workbook to Homeworkbook
HomeWorkBook = ActiveWorkbook.Name
Sheets("Q_Export_Data_Provides").Select
Range("A2:M100").Select
Selection.Copy
' Copy to IGS File
Workbooks.Open Filename:=IGSFilename
IGSWorkBook = ActiveWorkbook.Name
Sheets("Provides").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Go back to original temp file
Windows(HomeWorkBook).Activate
Sheets("Q_Export_data_changes").Select
Range("A2:K100").Select
Selection.Copy
' Copy to IGS file
Windows(IGSWorkBook).Activate
Sheets("Changes").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Go to original temp file
Windows(HomeWorkBook).Activate
Sheets("Q_Export_data_ceases").Select
Range("A2:E100").Select
Selection.Copy
' Copy to IGS file
Windows(IGSWorkBook).Activate
Sheets("Ceases").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Get current date
savedateday = Day(Date)
savedatemonth = Month(Date)
savedateyear = Year(Date)
' save the IGS file with todays date with no slashes
Savename = "c:\test\IGS Daily " & savedateday & savedatemonth & savedateyear
ActiveWorkbook.SaveAs Filename:=Savename
' Close the temp file
Windows(HomeWorkBook).Activate
ActiveWorkbook.Close False
Application.ScreenUpdating = True
End Sub
I have an Access button which runs 3 exports of queries to 3 seperate pages in an existing Excel file. This works fine.
What i wanted to do was have Access send the data from the 3 queries to an Excel template file automatically but it seems its easier for Excel to do this.
So the data is sent from Access to an Excel temp file. Then, in the temp file I have written some code which opens the template file, and copies data from the temp file, to the template file, saves the template file under a new name, and closes the temp file without saving it.
I have added some code to the button in Access to run a macro I have called AutoOpen. It seems the Aut

Anyway, the macro runs, but at the very end after the file has been saved, Access comes up with an "440 : Automation Error". Also it doesnt show the Excel file. Even after closing the Access error, clicking on the Excel file does not bring it up but I can close it by right clicking on the task bar and selecting close no problems. Its as if Access has preference.
So i'm not sure if it is the Access code or the Excel code which is causing the problem, im sure its something simple I have missed out, can anyone help?
Thanks
Gary
Access code:
==========
Private Sub Command35_Click()
Dim strReportTemplate As String
Dim strMacro As String
' set location of the XLS temp file
strReportTemplate = "c:\test\temp.xls"
' dump contents of 3 sep queries into sep pages on the Excel Temp file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_provides", strReportTemplate, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_changes", strReportTemplate, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_export_data_ceases", strReportTemplate, False
Dim XLApp As Object
' Open the XLS fle, make it visible, get control and run a macro called AutoOpen
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "c:\test\temp.xls"
.Application.Run "AutoOpen"
End With
End Sub
Excel Code
========
Sub AutoOpen()
Dim HomeWorkBook As Variant
Dim IGSFilename As Variant
Dim IGSWorkBook As Variant
Dim Savename As Variant
Dim savedateday, savedatemonth, savedateyear As Integer
' Set IGS Filename and location (this is the name of the template file to copy data into)
IGSFilename = "C:\test\IGS Daily.xls"
Application.ScreenUpdating = False
' set current Temp workbook to Homeworkbook
HomeWorkBook = ActiveWorkbook.Name
Sheets("Q_Export_Data_Provides").Select
Range("A2:M100").Select
Selection.Copy
' Copy to IGS File
Workbooks.Open Filename:=IGSFilename
IGSWorkBook = ActiveWorkbook.Name
Sheets("Provides").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Go back to original temp file
Windows(HomeWorkBook).Activate
Sheets("Q_Export_data_changes").Select
Range("A2:K100").Select
Selection.Copy
' Copy to IGS file
Windows(IGSWorkBook).Activate
Sheets("Changes").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Go to original temp file
Windows(HomeWorkBook).Activate
Sheets("Q_Export_data_ceases").Select
Range("A2:E100").Select
Selection.Copy
' Copy to IGS file
Windows(IGSWorkBook).Activate
Sheets("Ceases").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Get current date
savedateday = Day(Date)
savedatemonth = Month(Date)
savedateyear = Year(Date)
' save the IGS file with todays date with no slashes
Savename = "c:\test\IGS Daily " & savedateday & savedatemonth & savedateyear
ActiveWorkbook.SaveAs Filename:=Savename
' Close the temp file
Windows(HomeWorkBook).Activate
ActiveWorkbook.Close False
Application.ScreenUpdating = True
End Sub