Run-time error '429': ActiveX component can't create object From Excel to Word

[remeng]

Hi,

I'd like to create a macro to open a Word document from Excel.

I am receiving Run-time error '429': ActiveX component can't create object when I execute the code.

I don't have admin privileges to perform the regedit that is called out in this article. This also has to work on several computers.

https://support.microsoft.com/en-us/topic/you-receive-run-time-error-429-when-you-automate-office-applications-b6070e15-ea78-4349-2751-72bdb5f186f7

I tried the following code and I get an error on step 1.

1: Set oWord = CreateObject("Word.Application")

Sub test ()

Dim oWord As Word.Application
 Dim oExcel As Excel.Application
 
 On Error Goto err_handler
 
 1: Set oWord = CreateObject("Word.Application")
 2: Set oExcel = CreateObject("Excel.Application")
 
 ' ... some other code
 
 err_handler:
   MsgBox "The code failed at line " & Erl, vbCritical

End Sub

Is there another way to get past the error?

 

[remeng]

Update - There was different code out there that works.

Sub test_open_pathway()


Dim objWord As Object
Dim pathway As String

pathway = "C:\some folder\labels test.docx"

Debug.Print pathway

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open pathway



End Sub

 

[macropod]

The code in your first post employs early binding. Had you set a reference to Word via Tools|References in the VBA Editor?

 

[strongm]

Hmm. If they hadn't then

Dim Word As Word.Application

would have failed (with a 'user-defined type not defined' error), not the later

Set objWord = CreateObject("Word.Application")

Personally,. I'd start by running a repair against Word

 

[remeng]

Hi Paul,

I did activate it prior to posting the original issue.

What I learned was that Office has security restrictions that prevent Excel from directly running code in Word. The solution around that is to execute a separate macro in Word that is executed from a macro in Excel. This code was used for a print mail merge operation. I've stripped out the mail merge code since it can be added depending on what the user wants.

Excel Macro Code

Sub open_word_label_updates_mail_merge()

' Works to open labels doc

Dim objWord As Object
Dim pathway As String
Dim Report_to_Print As String
Dim excel_data_query_folder_file_pathway As String

ThisWorkbook.RefreshAll

On Error GoTo err_opening_file


' Sets the file / folder location

label_pathway = "C:\SOME FOLDER PATHWAY/SOME WORD DOC AS MACRO.docm"

Debug.Print label_pathway

excelPath = Application.ActiveWorkbook.FullName

Debug.Print excelPath

' Opens the word document

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open label_pathway


' Executes the macro by the specified name in the Microsoft Word Macro file

Report_to_Print_variable = "word_macro_name"

objWord.Run Report_to_Print_variable

Set objWord = Nothing


GoTo end_macro

err_opening_file:

    MsgBox "err opening word doc"


end_macro:


End Sub

Word Macro Code

Sub word_macro_name()


' Do some stuff


End Sub

I am now working to see how to pass a variable from Excel to Word. I've tried a few things, but they all fail out.

For both methods Iin Word 've used the Sub MACRO_NAME(VARIABLE)

I've tried the "MACRO_NAME.VARIABLE" method with the Word macro MACRO_NAME(VARIABLE)

Also "MACRO_NAME" , VARIABLE failed out.

Any suggestions for an alternate method of transferring the variable?

 

[combo]

I don't know the process you plan to automate, but some time ago I made an Excel application to deal with Word mail merge - export documents to 'pdf' format. Mail merge, for security reasons, is blocked in case of full automation - existing data source is not available (AFAIR it is still possible to programmatically add it).

My solution first tests if word is open and has a document. If not, user is asked to open a document with MM and try again, otherwise a list with available documents is filled. The user points to a document, code tests if MM is available, a list of fields is filled, user chooses index (for a part of file name). The application also allows mailing with attached files using index field and email address in the other field.

My conclusions after finishing this project:

• I put the full code in Excel (userform),
• in case of MM, it is much easier to ask user to open document with MM first,
• I used early binding (Word, Outlook), A function with GetObject was assigning result to strongly typed variable,
• in case of automating Outlook it is possible to programmatically open it and do mailing, however it is less annoying to ask user to open it and get existing instance.

 

[strongm]

>What I learned was that Office has security restrictions that prevent Excel from directly running code in Word

Not quite sure where you learned this. Perhaps you can post a link, since I am not aware of this and it would be useful to see.

There is a setting in each Office application that can prevent access to VBA project object module ("Trust access to the VBA project object module"), but this has a very specific purpose to prevent malicious alteration of the VBA project; it does not stop you using the project (i.e. you can still call macros)

1) This would not cause an error on Set oWord = CreateObject("Word.Application") as you describe in your OP
2) If it did somehow cause that error, then it would cause the same error on your most recent example, since you still call Set oWord = CreateObject("Word.Application") to get an instance of the Word object
3) If that setting was in effect and did block macros then your new example wouldn't work

So, something else is going on. Still, if you have somehow cobbled together a solution that sort of works, maybe you'll be happy with that ...

>how to pass a variable

objWord.Run Report_to_Print_variable, VARIABLE

should work.

 

[remeng]

Hi Strongm - here is the link to the root cause of the security issue. There is a SQL operation that activates the Word Macro. They recommend that you regedit, but the following method works without having to go and possibly damage the registry. Once I understood the root cause of the error, I made it so only variables passed from Excel to Word instead of direct print code by calling just the Word macro by name. I don't full understand why it works, but hey, it works.

You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source

Here is the working code that will go from Excel to Word. I had a string that was passed as a variable that was causing an issue. Once I corrected that, everything started to run correctly.

I tried to add notes and error msgbox to help explain what is going on.

Sub open_word_label_updates_mail_merge(mail_merge_to_print)

' Works to open labels doc - Use this shit

Dim objWord As Object
Dim pathway As String
Dim Report_to_Print As String
Dim data_query_to_print As String

On Error GoTo err_opening_file

' Alert msg that Word will close automatically once the labels have been printed.  This prevents errors when the macro runs next time.  This is intended to fire everytime that the macro / sub-macro is run

If MsgBox("NOTE:" & Chr(10) & Chr(10) & "Microsoft Word will automatically close once the macro is complete." & Chr(10) & Chr(10) & "It is recommended that you save all Word documents prior to launching this program." & Chr(10) & Chr(10) & "Would you like to continue running this program?", vbQuestion + vbYesNo, "SAVE WORD DOCUMENTS") = vbNo Then

    End

Else: End If


' Sets the file / folder location for the label Word documenet.  Note: Must be the drive pathway and not a SharePoint HTTP:\\ style pathway.  Navigate to the drive equivilent via the control panel setting.

label_pathway = "C:\LABEL PATHWAY\WORD DOCUMENT NAME.docm"

Debug.Print label_pathway

' Sets this file's pathway.  This is used in the Word macro to find the Power Query tables.  If there is an error, verify that this files pathway is not a SharePoint HTTP:\\ style pathway.  Navigate to the drive equivilent via the control panel setting.


excelPath = Application.ThisWorkbook.FullName


Debug.Print excelPath

' Opens the word document

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open label_pathway

' Executes the macro by the specified name in the Microsoft Word Macro file

Debug.Print mail_merge_to_print



'Example of text string CONCAT to be sent to the Word Macro file data_query_to_print = "SELECT * FROM `'ASMB Pull Query$'`"

data_query_to_print = "SELECT * FROM `'" & mail_merge_to_print & "$'`"

Debug.Print data_query_to_print



Report_to_Print_macro = "label_query_to_print"

' Command to Run the Report_to_Print_macro located in the Word label document.  Values after the first comma are passed variables.  Up to 30 variables can be passed.  The entire macro name and variables combined cannot exceed 255 characters.  This is a limit set by Excel.


objWord.Run Report_to_Print_macro, data_query_to_print, excelPath


' Force quits Word without saving the label file.  Any open Word documents will be closed without saving.  A warning message is at the top of the code alerting the user to this fact.

objWord.Quit SaveChanges:=wdDoNotSaveChanges



Set objWord = Nothing




GoTo end_macro

err_opening_file:

    MsgBox "An error occured when attempting to print labels." & Chr(10) & Chr(10) & "Make sure that Word is closed prior to launching this program.  Word will auto close when the macro is completed.", vbCritical + vbOKOnly, "ERROR PRINTING LABELS"

    ' The main type of error occurs when the Label macro template file is already openned.  Try closing Word and then run the macro again.
    ' Check that the call Word macro name and variables match the macro name and variables in the label Word document macro code.

    Exit Sub

end_macro:



End Sub

 

[strongm]

So, this is the first time you have mentioned SQL or that the error was a SQL-related

Indeed, your OP is very specific about this being a 429 error.

And the SQLSecurityCheck does not 'prevent Excel from directly running code in Word' - it simply alerts you that a SQL command is attempting to access a SQL Server backend

if you'd mentioned trying to remotely run a SQL query against SQL Server we'd have been able to advise you much faster, as this is a well know behaviour!

Still, you've got it working, so enjoy.

 

[remeng]

Hi strongm,

I didn't even know that it was a SQL issue until well after I got the 429 error and researched the hell out of it. Trust me, I'd have brought the SQL issue up once I learned about it.

Once I did find out about the SQL issue, I was able to google fu it and get a path forward.

Now onto researching how to print to a specific printer IP address. I'm working on finding a solution. If I get stuck, I'll be back for sure.

Mike