how do i automatically change pasted links in word

[kskinne]

I have a Word document that contains approximately 100 links to an Excel workbook. All the links go to different sheets and/or cells in the same workbook. Every quarter I have to create a new Word document, and a new Excel workbook, and then do the following in Word:

- go to the edit menu and select links
- select all the links
- press the change source button
- one by one double-click on the new source file (the sheet and cell references are exactly the same, only the workbook name changes)

I was wondering if there was a way to automate this process, right now it takes forever. Any help is appreciated, thank you.


kevin

[kskinne]

anyone have any ideas on this

[slowhand]

I had a similar problem

2 things first

1/ make a master version of the workbook (Template) - use this to fill in the data required

2/ Save your word document as a template (empty of data but with required fields)

when you first use the template make your links to the spreadsheet but use the macro recorder to copy what you do, save the the macro to the template

when you have gathered the information required save both the spreadsheet and the word document with a different name

then everytime after that use the master spreadsheet to collect the data - use the word template with the macro to collect the data

Hope this helps if not give me a shout and we'll see if theres any other way