I use “Invoke VBA” inside the “ExcelApplicationScope”.
The running time of the VBA is not certain, sometimes it takes over 10 minutes (i tested it without the Uipath).
As i run it inside the uipath inside the “ExcelApplicationScope”, the excel closed before the VBA completed.
May i know how to prevent it? set a delay time is not the best way as sometimes VBA completes within a minute if my file is small.
Error message:
“message”: "Invoke VBA: Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.Office.Interop.Excel._Workbook’. This operation failed because the QueryInterface call on the COM component for the interface with IID
VBA code:
Sub CopyWorkbook()
Dim aw As Workbook
Dim y As Workbook
Dim sh As Worksheet
Set aw = Application.ActiveWorkbook
Set y = Application.Workbooks.Open("D:\Working.xlsx")
For i = 1 To aw.Sheets.Count
Set sh = Nothing
On Error Resume Next
Set sh = y.Worksheets(aw.Worksheets(i).Name)
On Error GoTo 0
If TypeName(sh) <> "Nothing" Then
sh.Range("A:BF").Copy aw.Worksheets(i).Range("A1")
End If
Next i
Application.CutCopyMode = False
End Sub