Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating Word Application using Excel VBA: Run-time error '429': ActiveX component can't create object

I am trying to save Word docs using Excel VBA, but I get the error

"ActiveX component can't create object."

When I debug, the error comes from the line: Set wrdApps = CreateObject("Word.Application").

It was working, then it started giving me this error.

Sub saveDoc()

Dim i As Integer
For i = 1 To 2661:
    Dim fname As String
    Dim fpath As String

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    fname = ThisWorkbook.Worksheets(3).Range("H" & i).Value
    fpath = ThisWorkbook.Worksheets(3).Range("G" & i).Value

    Dim wrdApps As Object
    Dim wrdDoc As Object

    Set wrdApps = CreateObject("Word.Application")

    'the next line copies the active document- the ActiveDocument.FullName 
    ' is important otherwise it will just create a blank document
    wrdApps.documents.Add wrdDoc.FullName

    Set wrdDoc = wrdApps.documents.Open(ThisWorkbook.Worksheets(3).Range("f" & i).Value)
    ' do not need the Activate, it will be Activate
    wrdApps.Visible = False  

    ' the next line saves the copy to your location and name
    wrdDoc.SaveAs "I:\Yun\RTEMP DOC & PDF\" & fname

    'next line closes the copy leaving you with the original document
    wrdDoc.Close

    On Error GoTo NextSheet:
NextSheet:
    Resume NextSheet2
NextSheet2:
Next i

With Application
   .DisplayAlerts = True
   .ScreenUpdating = True
   .EnableEvents = True
End With

End Sub
like image 559
user2525309 Avatar asked Jun 26 '13 18:06

user2525309


People also ask

Can not create ActiveX component?

This behavior can occur if any of the following conditions are true: Data Access Objects (DAO) is not properly registered. One or more references are missing. There is a utility database reference that is not valid.


1 Answers

I recently had this happen to some code I had written. Out of nowhere (after running successfully for a few months), I would get the same Runtime Error '429'. This happened on two separate computers, the one I wrote and tested the code on months prior and the computer of the person who actually used the tool. It happened even though I used the original file on my machine and the user had been using his copy successfully for a few months, so I'm not convinced two separate files on two separate machines both got corrupted in the same manner. With that being said, I don't have a good explanation of why this occurred. Mine would happen on a similar line of code:

Dim objFSO as Object
Set objFSO = CreateObj("Scripting.FileSystemObject")

I had the reference to the scripting library included and had done this successfully in the past.

I was able to fix the problem by changing from late to early binding on that object:

Dim objFSO as New Scripting.FileSystemObject

I have been switching everything over to early binding for some time now but this was some legacy code. A nice short little explanation on the difference between the two can be found here: https://excelmacromastery.com/vba-dictionary/#Early_versus_Late_Binding

I'm not entirely certain why that fixed the problem, but hopefully it will help others in the future with similar issues.

like image 151
user2731076 Avatar answered Oct 02 '22 09:10

user2731076