Excel VBA program fails on second run on statement CreateObject accessing DLL

Question

I have some Excel 2010 VBA code running on a Windows 10 Pro computer. The code creates an object for a DLL, and at the end of the program the object is set to nothing. The code runs fine the first time, but if I run it again it crashes on the CreateObject line with the following error message:

Run-time error '-2147467259 (80004005) Automation error Unspecified error

It seems like the DLL object is not being released and that may be why it crashes when trying to create the object on a second run. I found this web page which may be related: https://support.microsoft.com/en-us/help/178510/excel-automation-fails-second-time-code-runs

Below is some sample code, and the line where it crashes is noted. Any idea how to troubleshoot/fix this? Could this problem be related to running Office 2010 on Windows 10? The code worked on my prior Windows 7 computer. By the way, I'm far from an expert in VBA so some of the terminology I'm using may be off.

Sub AddOverlayToPDF()
    Dim BasePath As String
    Dim InFolder As String
    Dim OutFolder As String
    Dim FileName As String
    Dim PDF, rslt

    BasePath = "C:\Users\macke\Desktop\"
    InFolder = BasePath & "Comm Charts - No Legend\"
    OutFolder = BasePath & "Comm Charts\"

    Set PDF = CreateObject("pdf.Meld") ' <-- *** THIS LINE FAILS ON SECOND RUN ***
    FileName = Dir(InFolder & "*")
    Do While Len(FileName) > 0
        Debug.Print FileName
        PDF.setRevOverlay
        PDF.setRepeat
        PDF.setOverlayPages ("9-11")

        PDF.setInFile "C:\Users\macke\Desktop\Legend Overlay.pdf"
        PDF.setInFile InFolder & FileName
        PDF.setOutFile OutFolder & FileName

        rslt = PDF.buildPDF
        If rslt < 0 And rslt <> -10 Then
            MsgBox ("Error " & rslt)
        End If
        PDF.setReset

        FileName = Dir
    Loop

    Set PDF = Nothing
End Sub

Does it make any difference if you add PDF.Close just before Set PDF = Nothing? — barrowc
– barrowc, Commented Mar 22, 2019 at 0:40
I added "PDF.Close" just before the "Set PDF = Nothing" line and got the following error message: Run-time error '9': Undefined subroutine &PDFMeld::untaint called at pdfmeld2.pl line 234. — Mark
– Mark, Commented Mar 22, 2019 at 17:40

ShellNinja · Accepted Answer · 2021-10-09 08:19:34Z

0

I have same error code -2147467259 (80004005) it turned out that the old Excel sheet my users were running is only compatible with Office x86.

I had made a new server with Office 2019 x64 - as soon as I reinstalled office to x86, the macros worked fine.

The ODBC instances was represented both in x32 and x64. Check if you might have x64 versus x86 issues

edited Oct 9, 2021 at 8:19

ShellNinja

6448 silver badges25 bronze badges

answered Oct 8, 2021 at 11:10

TroelsDK

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA program fails on second run on statement CreateObject accessing DLL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related