CODEWITHSUNDEEP
excelms-wordvba
codemonkey
codemonkey

Reputation: 103

VB Excel - When opening a word doc from VB with Excel I get a 'Variable not defined' error when trying to use ActiveDocument

I have a a requirement to open a Word doc from Excel and substitute text in a Text Box in the Word doc with text from the Excel spreadsheet.

I recorded a Macro with Word and it said to use ActiveDocument, however when I try to use this in my code within Excel, I get a Variable not defined error.

Here is my code -

Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True

objWord.Documents.Open "C:\Users\kmccorma\Desktop\ReportPage.doc"

ActiveDocument.Shapes("Text Box 12").Select

I tried objWord.Shapes but I get an error saying Object doesnt support this property.

I can see the Shapes property is available with ActiveChart, do I need to do something with ActiveChart to get this to work?

Still relatively new to VB, so any help would be much appreciated.

Upvotes: 1

Views: 1309

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

That is because, Excel doesn't recognize what ActiveDocument is. I would recommend using Objects. See this example.

Dim objWord As Object, objDoc As Object

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

Set objDoc = objWord.Documents.Open("C:\Users\kmccorma\Desktop\ReportPage.doc")

With objDoc.Shapes("Text Box 12")
    '~~> Do Something
    '.Select
End With

Upvotes: 1

Related Questions