wHAT AM i MISSING?

[Ziggy1]

I'm trying to get this code to work in the "on Click" Event for a button, but I get this error:

Filename or class name not found during Automation operation

This is the code:


Private Sub Command0_Click()

On Error GoTo Err_Command0_Click
Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook

Set xlsApp = CreateObject("Excel.Application")
Set xlswkb = GetObject("C:\Personal.XLS")

xlsApp.Application.Run "PERSONAL.XLS!FILEOPEN2"

xlsApp.Quit

Dim stDocName As String

stDocName = "FULL INTRANSIT Report"
DoCmd.OpenReport stDocName, acNormal

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click



End Sub


The code is supposed to go into the Personal.xls file to run the specified Macro code.


Any help is appreciated

Ziggy

 

[joebater]

Hi there

Just guessing but I don't see where xlswkb is actually used! You've used create object to explicitly select an xls file but then use a filename having no path or other information - is this file in the same disrectory as your database?

I would try something like:

Private Sub Command0_Click()
'ignore error trapping for this example

Dim xlApp as New Excel.Application
Dim strFileName as String, strMacroName as String

Set xlApp = New Excel.Application

'use the excel app to browse for a file
strFileName = xlApp.Application.GetOpenFile(... 'add filter for excel

'ensure that 'Cancel' has not been hit
If Not strFileName = "False" Then
'append the macro name to the file name
strMacroName = strFileName & "!FILEOPEN2"

xlApp.DoCmd.Run strMacroName

End If

xlApp.Quit
Set xlApp = Nothing

'then continue to open your form as normal

Fingers crossed
j.

 

[Ziggy1]

Thanks Joe,

you pointed me in the right direction, I did not complete the path to the personal.xls

It works now, except if Excel is open I get a message saying it can't find the Personal.xls file.

I modified the code to open an excel file that has an auto execute macro and it seems to work like that, so I might go that route.

Thanks again also thanks to Steve Fraccus who helped me with this code in the past.



Ziggy