I have a Word document containing numerous linked Excel tables and charts. This document needs to be copied and modified regularly and its linked Excel tables and charts need to be updated with new data. I am looking for a Macro that, when executed, will:
- ask to input in a text box text in the LINK field to be found (i.e.: mysheets\\xyz_20230103.xlsx" "Sheet1!);
- ask to input in another text box text to replace the text found in step 1 (i.e.: yoursheets\\xyz_20230203.xlsx" "Sheet1!);
- find and replace all occurrences;
- update all LINK fields and keep their formatting.
For greater precision, the complete Excel object LINK field is { LINK Excel.Sheet.12 "c:\\mysheets\\xyz.xlsx" "Sheet1!L9C3:L33C30" "" \a \p } and needs to be replaced with { LINK Excel.Sheet.12 "c:\\yoursheets\\abc.xlsx" "Sheet2!L9C3:L33C30" "" \a \p }.
I’ve tried ALT-F9 to display LINK field codes, followed by CTRL-H to find and replace then Ctrl-A to select all and F9 to update, but it doesn’t work; the text in the LINK fields changes, but the tables and charts don’t update.
I’ve also found numerous examples of VBA to do one part or another of what I need, but I’m a newbie with VBA and don’t know how to piece them together.
Any help will be greatly appreciated!