cancel
Showing results for 
Search instead for 
Did you mean: 

Excel automation - attaching to a Workbook exported from SAP freezes

Paweł_Imiołek
Level 4

Hello,

From some time ago we have been experiencing on our project machines being stuck due to some performance issue of Excel which loads files (even small) too long. What our bot does is export of data from a transaction in SAP GUI to an Excel Workbook. Then, the bot applies 'Attach to workbook' Action from the original MS Excel VBO object (which in fact invokes Open Instance Action). From time to time, the bot gets stuck at this Action. Probably it tries to attach to the Workbook before Excel is ready and that's the reason but we have no idea how to fix it. There are some other errors which get thrown like:

Internal : Could not execute code stage because exception thrown by code stage: The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))

It may be solved by a retry loop or, in a more sophisticated way, by using IMessageFilter::RetryRejectedCall. It happens in the global code, function OpenNamedObjectAA (line generating the error: Dim application As Object = workBook.Application)

Public Function OpenNamedObjectAA(workbookName As String) As Object
    Const OBJID_NATIVEOM = &HFFFFFFF0
    Dim IID_DISPATCH As New Guid("00020400-0000-0000-C000-000000000046")
    Dim workBook As Object = Nothing
    Do
        Dim XLhwnd As IntPtr = FindWindowEx(IntPtr.Zero, XLhwnd, "XLMAIN", Nothing)
        If IntPtr.Equals(XLhwnd, IntPtr.Zero) Then Exit Do
        Dim XLDESKhwnd As IntPtr = FindWindowEx(XLhwnd, IntPtr.Zero, "XLDESK", Nothing)
        Dim WBhwnd As IntPtr = FindWindowEx(XLDESKhwnd, IntPtr.Zero, "EXCEL7", Nothing)
        AccessibleObjectFromWindow(WBhwnd, OBJID_NATIVEOM, IID_DISPATCH, workBook)
        If workBook IsNot Nothing Then
            Dim application As Object = workBook.Application
            If application IsNot Nothing Then
                Try
                    application.Windows(workbookName).Activate()
                    Return application
                Catch ex As Exception
                    Continue Do
                End Try
            End If
        End If
    Loop
    Throw new Exception("Excel with workbook name '" & workBookName & "' not found.")
End Function

However, we are still not sure about the origin of the main problem discussed here - freeze of Blue Prism at the Action Open Instance and how to solve it. Do you have similar experience and know how to help us in this matter?

It is worth being added that the OpenNamedObjectAA function is invoked asynchronously by this code (OpenNamedObjectAA is the Operation😞

Private Function ExecWithTimeout(Of T)(timeout As Integer, name As String, operation As Func(Of T)) As T
    Dim ar = operation.BeginInvoke(Nothing, Nothing)
    If Not ar.AsyncWaitHandle.WaitOne(TimeSpan.FromSeconds(timeout)) Then
        Throw New TimeoutException(name & " took more than " & timeout & " secs.")
    End If
    return operation.EndInvoke(ar)
End Function
3 REPLIES 3

Paweł_Imiołek
Level 4

Additionally, we tried to precede Attach with a hard-coded sleep and it helped in most cases but still - this is a workaround making our Process work longer and we want to know the precise fix.

abhis3kh
Level 4

Hi @Paweł Imiołek

One suggestion from side would be: Kill all excels at start of the process. then Try an action "Wait for Process" action to check if excel process is exists after clicking export. Once it gives exists then you give a 5/10 secs sleep depending on the time SAP took to populate the excel then try attach.  Let me know if that works for you. 

Paweł_Imiołek
Level 4

Hello @abhis3kh,

Thanks for your proposals. 5/10 sec sleep is something we applied but we prefer to avoid a sleep here. In fact, there is already a sleep to wait for a SAP GUI pop-up to allow saving file in a specific directory. Killing Excel before is a good idea, however I am not sure if Wait for Process helps in this case, because the following code to catch an Excel window goes smoothly. It means that Excel process must be running. But we can try to add it - it shouldn't harm anything.

Dim XLhwnd As IntPtr = FindWindowEx(IntPtr.Zero, XLhwnd, "XLMAIN", Nothing)
If IntPtr.Equals(XLhwnd, IntPtr.Zero) Then Exit Do
Dim XLDESKhwnd As IntPtr = FindWindowEx(XLhwnd, IntPtr.Zero, "XLDESK", Nothing)
Dim WBhwnd As IntPtr = FindWindowEx(XLDESKhwnd, IntPtr.Zero, "EXCEL7", Nothing)
AccessibleObjectFromWindow(WBhwnd, OBJID_NATIVEOM, IID_DISPATCH, workBook)

Thank you,
Paweł