How do I automatically insert text from an Excel file into an exi.

[Lise]

If I am using a statistical software package (SAS) to do some analysis, which
I can export into excel, and then I want to update an artcle in MS Word with
figures from Excel, how would I do that?

So then all I would need to do is just update the excel sheet and the Word
file is automatically updated. I only want to update certain text in the Word
file.

thanks in advance!

 

[muyBN]

This is a format I used to do what you're asking:

{LINK Excel.Sheet.8 [drive]:\\[path]\\[filename].xls
[Worksheet]![CellNameOrReference] \a \t \* MERGEFORMAT}

There are a few ways to create this link,among them manually and through the
Insert menu. Creating it manually might take more time and effort but it
helps you learn the "code-behind" for later manipulation.

Manually: The "curly" { } brackets are actually created by pressing the Ctrl
then the F9 key to form a Word field (don't try to type them in except with
the Ctrl-F9 key combination). Then type your info as above between the field
brackets (don't type in square brackets--those are to show how you'll
individualize per your path, file, worksheetname, etc.). When finished,
select from opening to closing curly bracket, press F9 key; if you've
inserted correct path, data etc. in the "code-behind" within the curlies, the
results will show after pressing F9.

Insert menu (this is from a Word 2003 perspective; probably different if you
have 2007): Insert-Field, then select Link from the "Field Names:" list. In
the next column, Field Properties, type in the path name and file, then
choose Excel.Sheet below that. In the next column, take your choice of Field
Options. I would recommend the ones for automatically updating (first on
list) and preserving formatting (bottom). After pressing OK, the results of
your work should be shown. To toggle the "code-behind" and viewed results,
select the field and press the Shift and F9 keys.

Have fun, and let me know if this works.

 

[alborg]

Take a look at these articles:

http://msdn2.microsoft.com/en-us/library/yaa4ha8t(VS.80).aspx

http://word.mvps.org/FAQs/MacrosVBA/LinkTableWithGraph.htm

If you can't figure it out, I can make a test template for you...

Cheers,
Al

 

[old man]

Hi,

Here is a bit of code to start you off:

This assumes that the excel source spreadsheet has a range named 'sales2'
and the existing target, Word document, has a bookmark named 'sales'. This
will insert whatever is in the excel range sales into the Word document at
the bookmark location sales. Please add error checking....


Sub updatedoc()
Dim wordapp As Word.Application
Dim report1 As Document
Dim r1 As Word.Range

Set wordapp = New Word.Application
Set report1 = wordapp.Documents.Open("c:\report1.doc")
Set r1 = report1.Bookmarks("sales2").Range
r1.InsertAfter Range("sales").Text
report1.Close savechanges:=True

wordapp.Quit
Set wordapp = Nothing
End Sub

old man

 

[old man]

Hi

This works fine:

This assumes that a named range, 'sales' (the source of the data) exists in
the spreadsheet and in the word document a bookmark named, 'sales2' exists.
You want to add error checking to this routine and you want to erase the
prexisting info in the target before running this. You can use this code as a
basis for doing what you want.

old man

Sub updatedoc()
Dim wordapp As Word.Application
Dim report1 As Document
Dim r1 As Word.Range

Set wordapp = New Word.Application
Set report1 = wordapp.Documents.Open("c:\report1.doc")
Set r1 = report1.Bookmarks("sales2").Range
r1.InsertAfter Range("sales").Text
report1.Close savechanges:=True


wordapp.Quit
Set wordapp = Nothing
End Sub