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!
03-08-2023, 06:24 AM
VBA to find/replace in LINK fields (linked Excel objects) with use of text boxes
Similar Threads
Word relative link to OLE objects from Excel
Windows 10 
Office 2021
Threaded Mode