Excel Automation Problem With Add-Ins

[Guest]

Hello All--

Before I get to my problem, let me acknowledge that I understand what we are
doing is not supported by Microsoft (KB257757). This thread is not intended
to be a discussion on whether Microsoft should support a part of their
product that is available for use.

Anyhoo...

We have an application that uses Excel Automation to run scheduled reports
(that use an Excel Add-In). In order for the application to use the Add-In,
it must open it during initialization. We do so as follows (simplified):

Dim ExcelAPP As Object
Dim tempAddin As Object

Set ExcelAPP = CreateObject("Excel.Application")
ExcelAPP.DisplayAlerts = False
ExcelAPP.AlertBeforeOverwriting = False

On Error Resume Next
For Each tempAddin In ExcelAPP.AddIns
If tempAddin.Installed Then
ExcelAPP.Workbooks.Open (tempAddin.FullName)
End If
Next

The problem occurs when we try to open the Add-In - it hangs there, with no
error thrown. We know that it hangs on this line because we have put debug
code in to put something in a file before and after it.

We put some code into the Workbook_Open event of the Add-In that would put
debug messages into a file, and also tried putting a Sleep in there.
Sometimes we see the debug file, sometimes not. Sometimes it does hang when
we try to open the Add-In, sometimes it does not. It is not consistent from
machine to machine, or even on the same machine, making it seem like a timing
issue.

When running this code, we do hear a "beep," likely meaning that an
invisible error or warning dialog is up, and cannot be acknowledged. If we
could even see or find out what this dialog was, it may give us a clue to a
fix.

Thanks,
pagates

 

[Simon Murphy]

pagates
Automating Excel is fine, expecting it to run unattended on a server is the
prob - mainly because errors will be shown on the server screen only. In
development you should be able to see the server screen to see the error
messages.
Is it security settings ?
You can't make that dialog go away, or if you do it will default to
disabled.
If not then what does the addin (xla) do on install and on open?
You might also try a doevents after opening the addins to give things chance
to catch up.
What happens if you run the code from VBA rather than VB?
Also you should be able to step through this all in debug - what happens?
Also you may need to physically trigger the auto_open code depending which
version of Excel you are automating

If none of this helps post a bit more info about what the xla is doing and
where/when this vb code is running

cheers
Simon

 

[Peter T]

Only a guess but just because an addin appears installed doesn't confirm the
file exists. Normally when starting Excel manually an alert prompts to
remove a missing addin from the manager. But you have disabled alerts.
Perhaps a little check to confirm it does.

If tempAddin.Installed Then
s = tempAddin.FullName
If Dir(s) = tempAddin.Name Then
ExcelAPP.Workbooks.Open (tempAddin.FullName)
Else
Debug.Print s
End If
End If

Another possibility, some of the shipped addins do not return the path in
the full name.

Regards,
Peter T

 

[Guest]

Hello,

Thanks for the replies. Here is some more information (sorry for the
lengthy response).

The Add-In file does exist, and that is confirmed by putting a call to
"AddToDebugFile" (a sub that writes data to a file in the temp directory)
with the Add-In filename before the open.

As far as running "unattended," that is not quite true. This is an "all in
one" box containing both client and server. It is running invisibly. The
code that calls the Add-In is a VB6 ActiveX EXE, which in turn is called by
another Active EXE, which in turn is called by a Standard EXE running as a
service:
Scheduler Service -> Queue Manager -> Excel Automation Manager -> Excel

I don't believe that it is the security settings (unless the automation
security is different). We have added the registry settings that modify
security to allow Add-Ins to work, and I have also set Macro Security to Low.
When opening Excel as an interactive user, there are no warning dialogs.

Stepping through the Workbook_Open code via the debugger produces no error
dialogs, and behaves properly. However, I don't believe that we are even
getting into this code, because we had added some debug code to create a
debug file as the first step in that event.

Adding the DoEvents around the open was a good idea; however, it did not
help. There is still a dialog opening, invisibly.

Hopefully, I've given enough information to show what is happening, and
roughly how our application works (or doesn't work, in this case....).
Unfortunately, this is "inherited" code that needs to live on.

Thanks,
pagates

Hello All--

Before I get to my problem, let me acknowledge that I understand what we are
doing is not supported by Microsoft (KB257757). This thread is not intended
to be a discussion on whether Microsoft should support a part of their
product that is available for use.

Anyhoo...

We have an application that uses Excel Automation to run scheduled reports
(that use an Excel Add-In). In order for the application to use the Add-In,
it must open it during initialization. We do so as follows (simplified):

Dim ExcelAPP As Object
Dim tempAddin As Object

Set ExcelAPP = CreateObject("Excel.Application")
ExcelAPP.DisplayAlerts = False
ExcelAPP.AlertBeforeOverwriting = False

On Error Resume Next
For Each tempAddin In ExcelAPP.AddIns
If tempAddin.Installed Then
ExcelAPP.Workbooks.Open (tempAddin.FullName)
End If
Next

The problem occurs when we try to open the Add-In - it hangs there, with no
error thrown. We know that it hangs on this line because we have put debug
code in to put something in a file before and after it.

We put some code into the Workbook_Open event of the Add-In that would put
debug messages into a file, and also tried putting a Sleep in there.
Sometimes we see the debug file, sometimes not. Sometimes it does hang when
we try to open the Add-In, sometimes it does not. It is not consistent from
machine to machine, or even on the same machine, making it seem like a timing
issue.

When running this code, we do hear a "beep," likely meaning that an
invisible error or warning dialog is up, and cannot be acknowledged. If we
could even see or find out what this dialog was, it may give us a clue to a
fix.

Thanks,
pagates

 

[Peter T]

What happens if you run same code from a normal module in say Word on the
client machine, with no open instance of Excel. Include -

If tempAddin.Installed Then
s = tempAddin.FullName
debug.? Dir(s) = tempAddin.Name, s
'open code

Regards,
Peter T

Hello,

Thanks for the replies. Here is some more information (sorry for the
lengthy response).

The Add-In file does exist, and that is confirmed by putting a call to
"AddToDebugFile" (a sub that writes data to a file in the temp directory)
with the Add-In filename before the open.

As far as running "unattended," that is not quite true. This is an "all in
one" box containing both client and server. It is running invisibly. The
code that calls the Add-In is a VB6 ActiveX EXE, which in turn is called by
another Active EXE, which in turn is called by a Standard EXE running as a
service:
Scheduler Service -> Queue Manager -> Excel Automation Manager -> Excel

I don't believe that it is the security settings (unless the automation
security is different). We have added the registry settings that modify
security to allow Add-Ins to work, and I have also set Macro Security to Low.
When opening Excel as an interactive user, there are no warning dialogs.

Stepping through the Workbook_Open code via the debugger produces no error
dialogs, and behaves properly. However, I don't believe that we are even
getting into this code, because we had added some debug code to create a
debug file as the first step in that event.

Adding the DoEvents around the open was a good idea; however, it did not
help. There is still a dialog opening, invisibly.

Hopefully, I've given enough information to show what is happening, and
roughly how our application works (or doesn't work, in this case....).
Unfortunately, this is "inherited" code that needs to live on.

Thanks,
pagates

Hello All--

Before I get to my problem, let me acknowledge that I understand what we are
doing is not supported by Microsoft (KB257757). This thread is not intended
to be a discussion on whether Microsoft should support a part of their
product that is available for use.

Anyhoo...

We have an application that uses Excel Automation to run scheduled reports
(that use an Excel Add-In). In order for the application to use the Add-In,
it must open it during initialization. We do so as follows (simplified):

Dim ExcelAPP As Object
Dim tempAddin As Object

Set ExcelAPP = CreateObject("Excel.Application")
ExcelAPP.DisplayAlerts = False
ExcelAPP.AlertBeforeOverwriting = False

On Error Resume Next
For Each tempAddin In ExcelAPP.AddIns
If tempAddin.Installed Then
ExcelAPP.Workbooks.Open (tempAddin.FullName)
End If
Next

The problem occurs when we try to open the Add-In - it hangs there, with no
error thrown. We know that it hangs on this line because we have put debug
code in to put something in a file before and after it.

We put some code into the Workbook_Open event of the Add-In that would put
debug messages into a file, and also tried putting a Sleep in there.
Sometimes we see the debug file, sometimes not. Sometimes it does hang when
we try to open the Add-In, sometimes it does not. It is not consistent from
machine to machine, or even on the same machine, making it seem like a timing
issue.

When running this code, we do hear a "beep," likely meaning that an
invisible error or warning dialog is up, and cannot be acknowledged. If we
could even see or find out what this dialog was, it may give us a clue to a
fix.

Thanks,
pagates