Excel link with Word

[gboll]

I am creating a file that is like a menu that has descriptions in word and
then I was hoping to put field link in word to tie to a spreadsheet that I
could update the prices. How would I create the links in the word document
to take the info from my excel file?

 

[ryguy7272]

Insert > object > Create from file > Browse > ...choose the spreadhsheet that
you want to link to... > Insert

Does that do what you want?

Regards,
Ryan---

 

[gboll]

no, that brings in the entire worksheet

I have a file in excel that has all the prices like:
15900
16500
12300
etc

then the word document I have is this:

Basic Widget
Model........................................................................
XXXXX

Where the XXXXX is I would like to link to the spreadsheet so that the 16500
number is linked or embedded into the word document.

Then if I change all the prices it automatically updates my price book

 

[ryguy7272]

Ah, now I see what you want. OK, it is a little tricky, but hang in there
and you will get it!

In Excel, you need code such as this:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

My Cell B1 is a named range, and is is named 'BrokerFirstName'. My cell B2
is also a named range and it is named 'BrokerLastName'. Set it up any way
you want; for your specific purposes.

Make sure in Excel's VBE, you click Tools > References > Microsoft Word

Now, in Word, you need this;
Insert > Field > DocVariable > New Name (and name it BrokerFirstName, or
whatever you wish). Then click OK. Repeat for subsequent variables. Save
the Word document. Run the macro through Excel, search for the Word doc.
that you just created; it will open and populate with the data in the named
ranges in Excel. Control the DocVariables with Alt+F9 on the keyboard.
That's it! You're in business gboll!!

Regards,
Ryan---

PS, take a look at this when you have a chance:
http://word.mvps.org/

http://word.mvps.org/faqs/interdev/controlxlfromword.htm

http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm