Open Word from Excel, pass variables and text (VBA macro), remote-control Word

Question

I'm completely new to VBA, especially across different programs, but I have created myself a little real-world task that quickly got complicated, someone could give me some pointers or code snippets to try out to achieve the following:

I have an Excel file that's filled with names and numbers (see below) and I would like to transfer them individually to a Word document.

If I have highlighted cell A2 and click on [BUTTON], I want Word to open automagically and type out something like

--"Hi Mike, your current amount is $12.37 and you live in 23 One Street. Thanks."--

The amount should be printed in bold, and after that Word should save the file and close itself without further input needed.

Similarly, when I have selected A3, it should open another document, write the same text but with Julia's variables filled in, save it to a specified location and close.

   A         B           C 
1 Name    Address      Amount
2 Mike    23 One Way   $12.37
3 Julia   3949 Street  $39.23

[BUTTON]

So essentially, I guess, I'm trying to "remote-control" Word from within Excel and feed some variables from Excel into Word. I am at a complete loss how to do that, to be honest.

What I have found so far is this:

Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("C:\temp\[NAME AFTER SELECTED FIELD A2].docx")

wdApp.Visible = True

...

Now I don't know what to do! How to pass the standard text with the variables from the row of the selected field over to Word? How to format them (bold, Arial, red, etc.)? How to save under the specified filename?

Is this even possible to do? I know VBA is very powerful, so I hope you can help me out! I'm using Office 2013, so any caveats related to macro programming or VBA language should take that into account.

Thank you so much!

Create a pre-formatted template document in Word, with bookmarks which you can replace with the values from Excel. It's not clear from your question whether you need to creatre a new document each time, or if you're updating existing documents (where each person has a specific document). Here's how to set bookmark text: word.mvps.org/faqs/macrosvba/InsertingTextAtBookmark.htm — Tim Williams

gNerb gNerb · Accepted Answer · 2014-11-29T13:10:24

Don't use get/create object. My macros are all coded as follows:

dim wdApp as New Word.Application 'Always use a new instance
Set wdDoc = wdApp.Documents.Open("C:\temp\[NAME AFTER SELECTED FIELD A2].docx")
wdApp.visible = true

From there you simply work as you would in word. Passing variables is pretty simple as the datatypes exist on both sides (double, integer, string etc are all included in the standard VBA libraries that are included by default so no need to add references). So if I want to tell word to add a paragraph:

wdDoc.paragraphs.add
wdDoc.paragraphs(wdDoc.paragraphs.count).range.text = "Hello World!"

Want to add text from a specific range?

dim xlRange as Excel.Range
dim wdApp as New Word.Application 'Always use a new instance
Set wdDoc = wdApp.Documents.Open("C:\temp\[NAME AFTER SELECTED FIELD A2].docx")
wdApp.visible = true

set xlRange = activesheet.range("A1") 'Just as an example

wdDoc.paragraphs.add
wdDoc.paragraphs(wdDoc.paragraphs.count).range.text = xlRange.value

Finally, say you want to use a variable name form excel to open a specified word document:

dim name as string
name  =  selection.cells(1).value 'Assuming they selected the cell

dim wdApp as New Word.Application 'Always use a new instance
Set wdDoc = wdApp.Documents.Open("C:\temp\" & name & ".docx")
wdApp.visible = true

I know this is a year late, but hey, hope it helps.

As a final note: Use exit sub just prior to end sub in the word macro. Exit sub returns to caller where as end sub does not.

sub test
'do the word stuff
exit sub 'Stop it short of end sub
end sub

Open Word from Excel, pass variables and text (VBA macro), remote-control Word

2 Answers