update link source by article title

[muster]

Hi all,

I need to automate a word doc. that links to Excel. I'm ok with VBA but
never did any macro in Word.

The word doc. has a few links to Excel tables (for now suppose they are
all in one file). The links need to change every quarter or month. I
want them change automatically when the report title change from
something like Q106 to Q206. Or I can put the time in somewhere in the
word report and make links change corresponding to it.

Please give hints. Thank you.

 

[zkid]

It's difficult to tell if I interpreted your question correctly. Anyway, I
think the only way to accomplish this is to toggle on the field codes and
replace the number to change.

For example,

ActiveWindow.View.ShowFieldCodes = True

Then, assuming your link looks something like this:

{ LINK Excel.Sheet.8 "C:\\FolderName\\Q106.xls " "Sheet1!R1C1:R4C4" \a \p }

You would need to parse the number (Q106 becomes 106 and then add 100 to it
to get 206, or however you need to change it).

Add back the Q to get Q206. Now replace Q106 with the new number wherever
it appears in the document (you should tack on as much of the path as
possible just in case the number appears elsewhere).

Now turn off the field codes:

ActiveWindow.View.ShowFieldCodes = False

Good luck.