Catia VBA - Automation Error Get Object

Question

I am getting an Automation error, when Catia is trying to write values in a selected Excel sheet. It's a bit confusing because on the first try of the code there was no error and the values were in the Excel sheet.

I didn't change the code, but on the second try I get:

Run-time error '-2147417846 (8001010a)':  Automation error
"The Message filter indicated that the application is busy."

on the line: Set MyXL = GetObject(FPath)

Sub CATMain()
FPath = CATIA.FileSelectionBox("Select the Excel file you wish to put the value in", "*.xlsx", CatFileSelectionModeOpen)

If FPath = "" Then
Exit Sub
End If

Set xlApp = CreateObject("Excel.Application")

Set MyXL = GetObject(, "Excel.Application")
Set MyXL = GetObject(FPath)
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
        Dim oSelection As Selection
        Set oSelection = CATIA.ActiveDocument.Selection
        Dim oProduct As AnyObject

    On Error Resume Next
        Set oProduct = oSelection.FindObject("CATIAProduct")

            If (Err.Number <> 0) Then
                MsgBox "No selected product"
            Else

    On Error GoTo 0

        Dim oInertia As AnyObject
        Set oInertia = oProduct.GetTechnologicalObject("Inertia")

        Dim dMass As Double
        dMass = oInertia.Mass

        Dim dDen As Double
        dDen = oInertia.Density

    MsgBox oProduct.Name & ": Masse = " & CStr(dMass) & " KG" & ": Dichte = " & (CStr(dDen) / 1000) & " "

        MyXL.Application.Cells(1, 1).Value = "Masse"
        MyXL.Application.Cells(2, 1).Value = dMass
        MyXL.Application.Cells(1, 2).Value = "Dichte"
        MyXL.Application.Cells(2, 2).Value = "dDen"

MsgBox "Werte wurden in Excel eingetragen"
  End If
   End Sub

Can you share the full error message for "the Automation error"? — Nico Haase, Jul 06 '18 at 07:11
Run-time error '-2147417846 (8001010a)': Automation error, The Message filter indicated that the application is busy — , Jul 06 '18 at 07:14
I think the Problem is that when I first use the Macro, it opens Excel, but it doesnt Close it anymore, how can I Close it? — , Jul 06 '18 at 07:16
@detmar do you think it might need a dim? I define one for path variables... — Solar Mike, Jul 06 '18 at 07:17
@SolarMike On the first try, there are no Problems, so I think thats not necessary. It's a Problem with not closing Excel. The Sheet is open and when I want to manually open the Excel File it says that I cannot Change anything cause It's in use — , Jul 06 '18 at 07:19
try putting a close application for excel after your end if... — Solar Mike, Jul 06 '18 at 07:21
@SolarMike I've put it, but there is now a Write restriction on the File. I don't know how you call it on English. It's "Schreibschutz" in german. So I cannot save changes manually to the file, because the file is blocked. — , Jul 06 '18 at 07:31

score 1 · Answer 1 · answered Jul 06 '18 at 07:51

It appears you did not set Option Explicit - put it on the first line and it will help you avoid errors. (With it, the compiler will force you to declare all your variables. This will also mean that when you put it in, your code will not work unless you declare all variables.)

The first problem:

Set xlApp = CreateObject("Excel.Application")

Set MyXL = GetObject(, "Excel.Application")

You first create a new instance of Excel with CreateObject and store a reference to it in xlApp (which you subsequently do not use). Then you try to get a reference to an existing Excel instance with GetObject and store its reference in MyXL. This only works reliably because you first create a new instance. Otherwise you could not guarantee that there always is an Excel instance available.

A related problem is, that you don't release/close these instances. If you create an Excel instance, you need to close it with xlApp.Quit after you're done using it, otherwise it will linger around.
Be careful though with instances you took over with GetObject - calling MyXL.Quit will close the instance regardless of what other workbooks are open at that time.

Similarly, if you open a file this way, you need to make sure to close it afterwards. Otherwise you'll run into the problem you experience: Write protected files.

So, to mend your problem: Close all open instances of Excel (best done via Task Manager, as some of them might be invisible). Then adjust your code to only use one reference to an Excel.Application. And finally make sure to .Close the workbook after you've saved it and .Quit your Excel instance. This should hopefully prevent the error from reappearing.

'Dim xlApp As Excel.Application    ' early-bound declaration
'Set xlApp = New Excel.Application    ' early-bound assignment
Dim xlApp As Object    ' late-bound declaration
Set xlApp = CreateObject("Excel.Application")    ' late-bound assignment

'Dim wb As Workbook    ' early-bound declaration
Dim wb as Object
Set wb = xlApp.Workbooks.Open(FPath)

' stuff you want to do with the workbook

wb.Close SaveChanges:=True
xlApp.Quit

If you can add a reference to the Excel object model in you Catia VBA project (not sure about that), you can comment out the late-bound lines and use the early-bound lines instead. That way you gain the very useful IntelliSense for the Excel objects. Which makes it so much easier to code.

score 0 · Answer 2 · answered Jul 06 '18 at 07:47

0

Thank you guys! I've solved the Problem with simply adding the code:

Workbook.Close SaveChanges:=True

answered Jul 06 '18 at 07:47

Catia VBA - Automation Error Get Object

2 Answers2