User Defined Type Not Defined- Controlling Word from Excel
I Am Trying to Do Some Relatively Simple Copy and Pasting from Excel 2007 into Word 2007. I've Looked Through This Site and Others, and Keep Getting Hung up on...
I am trying to do some relatively simple copy and pasting from Excel 2007 into Word 2007. I've looked through this site and others, and keep getting hung up on the same thing- the third line n the code below keeps giving me the "User type note defined" error msg. I am really confused since I just lifted this from another solution (and had similar issues with other solutions I tried to lift). Could someone please educate me on what is causing the error, and why?
Sub ControlWord()
' **** The line below gives me the error ****
Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.12")
appWD.Visible = True
'Find the last row with data in the spreadsheet
FinalRow = Range("A9999").End(xlUp).Row
For i = 1 To FinalRow
' Copy the current row
Worksheets("Sheet1").Rows(i).Copy
' Tell Word to create a new document
appWD.Documents.Add
' Tell Word to paste the contents of the clipboard into the new document
appWD.Selection.Paste
' Save the new document with a sequential file name
appWD.ActiveDocument.SaveAs Filename:="File" & i
' Close this new word document
appWD.ActiveDocument.Close
Next i
' Close the Word application
appWD.Quit
End Sub
2 Answers
This answer was mentioned in a comment by Tim Williams.
In order to solve this problem, you have to add the Word object library reference to your project.
Inside the Visual Basic Editor, select Tools then References and scroll down the list until you see Microsoft Word 12.0 Object Library. Check that box and hit Ok.
From that moment, you should have the auto complete enabled when you type Word. to confirm the reference was properly set.
As per What are the differences between using the New keyword and calling CreateObject in Excel VBA?, either
use an untyped variable:
Dim appWD as Object appWD = CreateObject("Word.Application")
or
Add a reference to
Microsoft Word <version> Object Libraryinto the VBA project viaTools->References..., then create a typed variable and initialize it with the VBANewoperator:Dim appWD as New Word.Applicationor
Dim appWD as Word.Application <...> Set appWd = New Word.ApplicationCreateObjectis equivalent toNewhere, it only introduces code redundancy
A typed variable will give you autocomplete.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
