Access to Excel Macro Automation Error (1 Viewer)

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 Autpen 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 a feeling it's the line

ActiveWorkbook.Close False

that is causing the issue for you. Generally speaking when you are automating another application it's best for you to close it the same way you opened it. So instead of having the Macro close the workbook use the application object you created to close the workbook.

Also make sure you set your excel application object = nothing otherwise you may end up with a hidden excel instance running in the background.

So you mean in Access - add the line to close the excel application?

What would the code for this be?
And also for setting the excel application to nothing, would this be something like set xlapp = nothing?

Thanks
Gary

So you mean in Access - add the line to close the excel application?

Click to expand...

Yes

What would the code for this be?

Click to expand...

Make sure you close the work book before you destroy the application object.

And also for setting the excel application to nothing, would this be something like set xlapp = nothing?

Click to expand...

Correct:

oooh I could kiss you on the head lol! Yes it works!

Excel stays open however, with just a blank screen, is it possible to actually totally quit excel from an Access command, such as application.xlapp.quit as a guess?

garyholc said:

oooh I could kiss you on the head lol! Yes it works!

Excel stays open however, with just a blank screen, is it possible to actually totally quit excel from an Access command, such as application.xlapp.quit as a guess?

Click to expand...

Should just be xlapp.Quit

Thank you very much for your help, much appreciated

Yours was the gift that kept on giving DJkarl. You solved my problems as well...thanks!

Hi
I tried to use the code but I got an error "Run-time error '1004': The macro 'Module1' cannot be found." As you suggested, I have closed the workbooks, set excel application to nothing and quit.

Private Sub Command12_Click()
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = true
.Workbooks.Open "C:\abcd\Treated V8.xls"
.Application.Run "Module1"
.Workbooks.Close
End With
Set XLApp = Nothing
XLApp.Quit
End Sub

Your will be much appreciated with any help.

Thanks
Prakash

You run a function or sub, not a module. I assume Module1 is the name of the module, you should have a sub or function in that module you wish to run
i.e.
here you would call RunME in this example.

DJkarl,
Thanks for your prompt reply. I still couldn't get my head around it. I have tried like :

1. In ACCESS
Private Sub Command18_Click()
................................
Call RunMe
..........................
End sub

2. In ACCESS (couldn't call Excel Function from Access)
Function RunME()
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
With XLApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "C\....\Treated V8.xls"
.Application.Run "RunExcel" '<----Sub of Excel (Treated V8.xls)?
.Workbooks.Close
End With
Set XLApp = Nothing
XLApp.Quit
End Function

3. In Excel
Sub RumExcel()
......
End Sub

I will be very pleased with your suggestion as I already spent hours on this.

Thank you.

Kind regards
Prakash