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
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With