Code in Module crashes but not in form

[poporacer]

I have a form that calls a function in a module that opens a spreadsheet, looks at the value of one cell and returns a variable. Then the code continues and does a similiar thing. I call the function in the Module from several different forms and that is the reason I have it in a module. The code works fine if it is a function in the form. As soon as I move the function to the module and delete the function from the forms, it crashes. I get an error: Automation Error The Remote procedure call failed. And then when that messagebox closes, I get: Error(91) Object variable or With block not set. If I set a breakpoint in the code and step through it, it does not crash. If I set a breakpoint and then continue, it still does not crash. I can make it work with the same code stored in each form that needs it, but it would be nice if I could have one instance of the code in the module.

Any ideas?

Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object
isExcelOpen = True
 
On Error Resume Next
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
isExcelOpen = False
Err.Clear
Set oXLApp = CreateObject("Excel.Application")
End If
 
On Error GoTo PROC_ERR
 
oXLApp.Visible = False
Set oXLBook = oXLApp.Workbooks.Open(gAlphaPath)
Set oXLSheet = oXLBook.Sheets(1)
 
'Do stuff here
 
oXLApp.Visible = True
oXLBook.Close
If isExcelOpen = False Then
oXLApp.Quit
End If
 
Set oXLBook = Nothing
Set oXLSheet = Nothing
Set oXLApp = Nothing

 

[Solo712]

I have a form that calls a function in a module that opens a spreadsheet, looks at the value of one cell and returns a variable. Then the code continues and does a similiar thing. I call the function in the Module from several different forms and that is the reason I have it in a module. The code works fine if it is a function in the form. As soon as I move the function to the module and delete the function from the forms, it crashes. I get an error: Automation Error The Remote procedure call failed. And then when that messagebox closes, I get: Error(91) Object variable or With block not set. If I set a breakpoint in the code and step through it, it does not crash. If I set a breakpoint and then continue, it still does not crash. I can make it work with the same code stored in each form that needs it, but it would be nice if I could have one instance of the code in the module.

Any ideas?

Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object
isExcelOpen = True
 
On Error Resume Next
[COLOR=red]Set oXLApp = GetObject("Excel.Application")[/COLOR]
If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
isExcelOpen = False
Err.Clear
Set oXLApp = CreateObject("Excel.Application")
End If
 
On Error GoTo PROC_ERR
 
oXLApp.Visible = False
Set oXLBook = oXLApp.Workbooks.Open(gAlphaPath)
Set oXLSheet = oXLBook.Sheets(1)
 
'Do stuff here
 
oXLApp.Visible = True
oXLBook.Close
If isExcelOpen = False Then
oXLApp.Quit
End If
 
Set oXLBook = Nothing
Set oXLSheet = Nothing
Set oXLApp = Nothing

You are missing the statement in red. That may be your problem.

Best,
Jiri

 

[poporacer]

That is plain weird... I looked at the code that I copy and pasted and that line is in there. I am not sure why it didn't post unless I accidentally deleted that line after I pasted it in my post.
My version is slightly different

[COLOR=#ff0000]Set oXLApp = GetObject(,"Excel.Application")[/COLOR]

I have a comma in there because the object is the second parameter. I will try it without the comma to see if it makes a difference.

So that line does exist in my code that is crashing. Any other ideas?

 

[spikepl]

Show entire function/subroutine, not just snippets.

Update:

And disable your error handler for debugging

 

[Solo712]

That is plain weird... I looked at the code that I copy and pasted and that line is in there. I am not sure why it didn't post unless I accidentally deleted that line after I pasted it in my post.
My version is slightly different

[COLOR=#ff0000]Set oXLApp = GetObject(,"Excel.Application")[/COLOR]

I have a comma in there because the object is the second parameter. I will try it without the comma to see if it makes a difference.

So that line does exist in my code that is crashing. Any other ideas?

The other idea that I have is to insert a Wait, Sleep (or do nothing) function after the "Get/Create" handler. Give the automation server a little time before talking to it. It works for me on some computers where I get this error. Try 50ms to begin with and increase by 20 ms. If you still get an error after 200 ms then that won't work.

Best,
Jiri