Referencing already opened word document in VBA

0
votes

I am using Excel VBA to open a word Template and it populates the template with information from an excel spreadsheet. From there, I have a button on the spreadsheet that I want to then populate another line in the word document whenever it is clicked. The problem I am encountering is that when I run the button macro it just opens up another word document and pastes the button information instead of doing it on the already opened document. I will attach my code below and I believe it is an easy fix I just can't seem to find a way around it.

Sub RepairCal()

Dim objWord As Object
Dim objDoc As Object

    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    Set objDoc = objWord.Documents.Open("C:\Users\z003narc\Documents\Gage Lab Form Template.docm")

objWord.Activate

With Selection

   objWord.ActiveDocument.Selection.MoveDown count:=6
   objWord.ActiveDocument.Selection.MoveRight count:=5
   objWord.ActiveDocument.Selection.TypeText Text:="Repair and Calibration"

End With

End Sub
2
vbaexcelms-word
If your host is Excel, Selection is referring to what's selected in Excel... but that With Selection block isn't doing anything, just remove it. Your life would be easier if you added a reference to the Microsfoft Word type library, and worked with a Word.Application and Word.Document instead of an Object. – Mathieu Guindon

2 Answers

0
votes

Try this: 

With objWord 

   .Selection.MoveDown count:=6
   .Selection.MoveRight count:=5
   .Selection.TypeText Text:="Repair and Calibration"

End With
0
votes

Are you saying you already have the document open when you run this every time? The way you wrote the code, it will do this:

1) Open new blank Microsoft Word process

2) In the new process, opens the workbook you have chosen

3) Makes the new process the active window

4) does stuff

If you want it to attach to the file you already have open, you would need to do GetObject instead of CreateObject. So change:

Set objWord = CreateObject("Word.Application")

To 

Set objWord = GetObject(, "Word.Application")

This will attach to the currently open process, then open the workbook in that process. However, it will still open up a fresh copy of that document each time, because that's what you're telling it to do.