Information From Excel97 into Word97

[Tappie]

I recieve several Excel spreadsheets on a daily basis containing technical information, this information is then extracted from the spreadsheet and placed into a Word document. A seperate Word document gets created for each spreadsheet which is exactly what I want and the data that gets extracted from Excel always comes from the same cells.

I can use copy and paste special etc. in order to insert the information into the Word template however I have to do this each time.

1. As I am using a word template and the data in the spreadsheet will always be in the same cells is it possible to just update all of the 'paste special' links in one go, in order to point it at a different spreadsheet ?

2. When I use the 'paste special' function to paste text into word from merged cells (Formatted Text RTF and paste it as a link this is required), a tab gets inserted after the text for each of the merged cells. Is there any way around this ?

Thanks

Tappie

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Tappie,

This macro may help you with the Links question:

' Macro created 26/10/01 by Doug Robbins to update links in a document
'
Dim alink As Field, linktype As Range, linkfile As Range
Dim linklocation As Range, i As Integer, j As Integer, linkcode As Range
Dim Message, Title, Default, Newfile
Dim counter As Integer

counter = 0
For Each alink In ActiveDocument.Fields
If alink.Type = wdFieldLink Then

Set linkcode = alink.Code
i = InStr(linkcode, Chr(34))
Set linktype = alink.Code
linktype.End = linktype.Start + i
j = InStr(Mid(linkcode, i + 1), Chr(34))
Set linklocation = alink.Code
linklocation.Start = linklocation.Start + i + j - 1
If counter = 0 Then
Set linkfile = alink.Code
linkfile.End = linkfile.Start + i + j - 1
linkfile.Start = linkfile.Start + i
Message = "Enter the modified path and filename following this
Format " & linkfile
Title = "Update Link"
Default = linkfile
Newfile = InputBox(Message, Title, Default)
End If
linkcode.Text = linktype & Newfile & linklocation
counter = counter + 1
End If
Next alink

You may also want to look at the article "Control Excel from Word" at:

http://www.mvps.org/word/FAQs/InterDev/ControlXLFromWord.htm

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.


Hope this helps
Doug Robbins - Word MVP

I recieve several Excel spreadsheets on a daily basis containing technical

information, this information is then extracted from the spreadsheet and
placed into a Word document. A seperate Word document gets created for each
spreadsheet which is exactly what I want and the data that gets extracted
from Excel always comes from the same cells.

I can use copy and paste special etc. in order to insert the information

into the Word template however I have to do this each time.

1. As I am using a word template and the data in the spreadsheet will

always be in the same cells is it possible to just update all of the 'paste
special' links in one go, in order to point it at a different spreadsheet ?

2. When I use the 'paste special' function to paste text into word from

merged cells (Formatted Text RTF and paste it as a link this is required), a
tab gets inserted after the text for each of the merged cells. Is there any
way around this ?