Population Word Variables from Excel

[DJM80]

Hi, I an ex-programmer who has been away from the programming world for a few years and need some assistance for a project.

I am trying to create user defined variables in Word for which I can set values from an excel file.

I want to create and use Word variables such as unemployment, inflation, costofliving, etc whose values should be set from an excel file. Once I have set these values, I want to be be able to type a sentence such as: The unemployment rate in Boston is 'unemployment'.

I will be using these numbers in my word document frequently and hence want to make them a variable so that I dont make a mistake typing them in several times.

Its allright if you dont have the complete answer. Nudges in the right direction are also appreciated.

Best,
DJM

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DJM80]

Havent started coding so far. I am hoping to get some sample that I can jump off from.

 

[macropod]

Hi DJM80,

If you're using a table in Excel, holding different records on each line, with a column for each data field, a mailmerge mmight be the way to go. Conversely, if you're always referencing the same Excel cells, a simple set of LINK fields between the Word and Excel files might suffice. Neither of these approach requires any vba.


Cheers
[MS MVP - Word]

 

[fumei]

I am trying to create user defined variables in Word for which I can set values from an excel file."

macropod's approach works fine, but if you really want to use VBA, it is not all that difficult. However, we need some details.

Do you want to create Document variables on the fly, via code, from Excel? It could be done, but if you just want to have them...then create them in the Word document first. Once they are created, even with a Value of "", you can easily use them (or change the Value) from Excel.

Option Explicit

Sub Yadda()
Dim appWord As Word.Application
Dim aDoc As Word.Document


Set appWord = CreateObject("Word.Application")
Set aDoc = appWord.Documents.Open _
      (Filename:="c:\testarray3.doc")

[COLOR=red]' set the variable that [b]already exists[/b] with a Value
' this demo is explicitly putting a text value[/color red]
aDoc.Variables("Unemployment").Value = "Yadda"

[COLOR=red]' Selection is a property of the application
' NOT the activeDocument, so use appWord[/color red]
With appWord
   [COLOR=red]' go the end of the document[/color red]
   .Selection.EndKey Unit:=wdStory
   [COLOR=red]'  type in text plus variable Value[/color red]
   .Selection.TypeText "The unemployment rate in " & _
      "Boston is " & aDoc.Variables("Unemployment").Value
End With

aDoc.Save
aDoc.Close
Set aDoc = Nothing
appWord.Quit
Set appWord = Nothing

End Sub

This assume the Word file testarray3.doc already has a variable named "Unemployment".

The code creates an instance of Word, opens the file, sets the variable "Unemployment" with a Value of "Yadda", goes to the end of the document and enters the text plus variable Value. It then saves and closes the file, and destroys the objects.

This assumes early binding and the proper Reference to Word is in the Excel module.

Result?

"The unemployment rate in Boston is Yadda"

You CAN also create the document variables from Excel, if you need to.

Option Explicit

Sub Yadda()
Dim appWord As Word.Application
Dim aDoc As Word.Document


Set appWord = CreateObject("Word.Application")
Set aDoc = appWord.Documents.Open _
      (Filename:="c:\testarray3.doc")

[COLOR=red]' set the variable that [b]already exists[/b] with a Value
' this demo is explicitly putting a text value[/color red]
aDoc.Variables("Unemployment").Value = "Yadda"

[COLOR=red]' CREATE the variable "Car" with a Value[/color red]
aDoc.Variables.Add Name:="Car", _
   Value:="Ferrari 550 Maranello"
[COLOR=red]' Selection is a property of the application
' NOT the activeDocument, so use appWord[/color red]
With appWord
   [COLOR=red]' go the end of the document[/color red]
   .Selection.EndKey Unit:=wdStory
   [COLOR=red]'  type in text plus variable Value[/color red]
   .Selection.TypeText "The unemployment rate in " & _
      "Boston is " & aDoc.Variables("Unemployment").Value _
         & vbCrLf _
         & "I want to drive a " & aDoc.Variables("Car").Value
End With

aDoc.Save
aDoc.Close
Set aDoc = Nothing
appWord.Quit
Set appWord = Nothing

End Sub

Result?

The unemployment rate in Boston is Yadda
I want to drive a Ferrari 550 Maranello

I do want to reiterate that macropod's approach - depending on what your actual requirements are - may be a better/easier way to go.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry