Mysterious ActiveX component error

[Engberg]

I'm working on a document search tool for internal use at my company. The tool consists of one main workbook which contains all of the code and the interface (a UserForm), and a database.xlsx which is hidden and only open in the background and contains information and search terms for each document that can be searched for/opened.

While testing, I've ran into Error 429: ActiveX component can't create object. This only happens in a very specific scenario:

1. Have any other Excel spreadsheet open
2. Open the search tool workbook
3. Close the search tool workbook
4. Opening the search tool workbook once more gives me Error 429 in Workbook_Open() on Line 0.

If I don't have another spreadsheet open, or close the other spreadsheet after opening the search tool for the first time, the error doesn't occur.

Any ideas on how to remedy this?

 

[RoryA]

It's usually easier to help with code that we can see...

 

[Engberg]

It's usually easier to help with code that we can see...

True. This is what happens on open and close. Workbook_Open() runs automatically when the file is opened, UserForm_QueryClose runs when the user form is closed (the Excel application is hidden, the user is only supposed to see the user form from start to finish)

Private Sub Workbook_Open()
On Error GoTo errhand
    Application.ScreenUpdating = False
    dataBasePath = "****CENSORED FILE PATH****"
    GetFileNames (dataBasePath)
    ThisWorkbook.Worksheets("SETUP").Activate
    
    Set dataBaseWB = Workbooks.Open("****CENSORED FILE PATH****\databas.xlsx")
    ThisWorkbook.Activate
    Application.Visible = False 
    UserForm1.Show
    Application.ScreenUpdating = True

Exit Sub
errhand:
    Call ErrorHandler("ThisWorkbook: Workbook_Open")
    Resume Next
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then

        dataBaseWB.Close 'SaveChange:=False
        Call WriteToSequenceLog("-------------------- " & Time & " " & Application.UserName & " APPLIKATIONEN STÄNGS" & vbNewLine)
        Call QuitApplication

    End If
End Sub

Public Function QuitApplication()
On Error GoTo errhand

  If Workbooks.Count > 1 Then
      ThisWorkbook.Close
  Else
      ThisWorkbook.Saved = True
      Application.Quit
  End If

Exit Function
errhand:
    Call ErrorHandler("Public Function: QuitApplication")
    Resume Next
End Function

 

[RoryA]

And what line is causing the 429 error? I don't see any likely suspects there, so my guess would be something in GetFileNames or in the userform's Initialize event.

 

[Engberg]

Ah yes, I forgot that Erl doesn't give you anything unless you manually number your lines. Apparently it is UserForm1.Show it is having trouble with.

 

[RoryA]

What's the code in the Initialize event of the form?

 

[Engberg]

What's the code in the Initialize event of the form?

I've used Activate instead, I can't remember if I chose that over Initialize for a reason or haphazardly.

Private Sub UserForm_Activate()
On Error GoTo errhand
    dataBasePath = "***CENSORED FILE PATH***"
    If Not IsEmpty(allFileNames) Then
        UserForm1.Label7.Caption = "Antal inlästa dokument: " & UBound(allFileNames)
    End If


    Call WriteToSequenceLog("-------------------- " & Date & " " & Time & " " & Application.UserName & " APPLIKATIONEN ÖPPNAS")
    noOfFilters = 5
   
Exit Sub
errhand:
    Call ErrorHandler("UserForm1: UserForm_Activate")
    Resume Next
End Sub

Public Function WriteToSequenceLog(inputText As String)

    filePath = dataBasePath & "***CENSORED FILE PATH***, a .txt file"
    Open filePath For Append As #1
    Print #1, inputText
    Close #1
End Function

Public Function GetFileNames(ByVal FolderPath As String) As Variant
On Error GoTo errhand
    Dim Result As Variant, Result2 As Variant, i As Integer, MyFile As Object, MyFSO As Object, MyFolder As Object, MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(FolderPath)
    Set MyFiles = MyFolder.Files
    ReDim Result(0 To MyFiles.Count)
    ReDim Result2(0 To MyFiles.Count)
    i = 0
    noOfDocs = 0
    For Each MyFile In MyFiles
        Result(i) = MyFile.Name
        Result2(i) = MyFile.DateLastModified
        'Debug.Print (Result(i))
        i = i + 1
        noOfDocs = noOfDocs + 1
    Next MyFile
    GetFileNames = Result
    allFileNames = Result
    allModifiedDates = Result2
    If Not IsArray(allFileNames) Then
        MsgBox ("Kan ej läsa av filerna i databasen")
    End If
Exit Function
errhand:
    Call ErrorHandler("Public Function: GetFileNames")
    Resume Next
End Function

 

[RoryA]

Can you set your error handling options in the VBE to break in class module, then try your code again to see the real offending line of code?

 

[Engberg]

Can you set your error handling options in the VBE to break in class module, then try your code again to see the real offending line of code?

I changed the setting, but I don't notice any difference. Is there anything else I have to do? This is new to me. I appreciate your patience

 

[RoryA]

So it still breaks on Userform1.Show? If you then press f8 does it just repeat the error message, or move to a different line of code?