Find & Replace a string contained in a link

[Guest]

I have a spreadsheet with numerous external links to numerous files. A
typical link would contain the following string:

8. August\[Project 1 Aug.xls]Data Tab'$L$25

I would change string to:

9. September\[Project 1 Sep.xls]Data Tab'$L$25

The constraint is that the filename also changes throughout the spreadsheet,
for instance, also want the following found and replaced:

OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25

OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25

etc., etc.,

I have tried using the following wildcard find/replace:
FIND: 8. August*Aug
REPLACE: 9. September*Aug

But while the find works, the replace just produces a "formula you typed
containes an error" message.

Any pointers? I'm sure this kind of find & replace is possible in Word...

Thanks,
Matt

 

[Dave Peterson]

How about just using
Edit|links|Change sources.

I have a spreadsheet with numerous external links to numerous files. A
typical link would contain the following string:

8. August\[Project 1 Aug.xls]Data Tab'$L$25

I would change string to:

9. September\[Project 1 Sep.xls]Data Tab'$L$25

The constraint is that the filename also changes throughout the spreadsheet,
for instance, also want the following found and replaced:

OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25

OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25

etc., etc.,

I have tried using the following wildcard find/replace:
FIND: 8. August*Aug
REPLACE: 9. September*Aug

But while the find works, the replace just produces a "formula you typed
containes an error" message.

Any pointers? I'm sure this kind of find & replace is possible in Word...

Thanks,
Matt

 

[Guest]

That is the solution that I am currently using. The problem is that there are
over 30 files that are linked (and growing) to this master file. Thus using
the "Change Sources" solution is getting more and more time consuming (and
subject to easy mistakes due to the similarity of the file names).

I need a more efficient and robust solution. Perhaps VBA is the only
solution? If so, I'd appreciate some pointers in this area...

Matt

How about just using
Edit|links|Change sources.

I have a spreadsheet with numerous external links to numerous files. A
typical link would contain the following string:

8. August\[Project 1 Aug.xls]Data Tab'$L$25

I would change string to:

9. September\[Project 1 Sep.xls]Data Tab'$L$25

The constraint is that the filename also changes throughout the spreadsheet,
for instance, also want the following found and replaced:

OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25

OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25

etc., etc.,

I have tried using the following wildcard find/replace:
FIND: 8. August*Aug
REPLACE: 9. September*Aug

But while the find works, the replace just produces a "formula you typed
containes an error" message.

Any pointers? I'm sure this kind of find & replace is possible in Word...

Thanks,
Matt

 

[Dave Peterson]

I can't think anything more robust than Edit|links|change links.

If you did edit|replaces, you could make a typo in the "to" field and each of
the formulas with errors would cause you to have to dismiss a dialog box.

Maybe you could automate that edit|links|change links procedure.

That is the solution that I am currently using. The problem is that there are
over 30 files that are linked (and growing) to this master file. Thus using
the "Change Sources" solution is getting more and more time consuming (and
subject to easy mistakes due to the similarity of the file names).

I need a more efficient and robust solution. Perhaps VBA is the only
solution? If so, I'd appreciate some pointers in this area...

Matt

How about just using
Edit|links|Change sources.

I have a spreadsheet with numerous external links to numerous files. A
typical link would contain the following string:

8. August\[Project 1 Aug.xls]Data Tab'$L$25

I would change string to:

9. September\[Project 1 Sep.xls]Data Tab'$L$25

The constraint is that the filename also changes throughout the spreadsheet,
for instance, also want the following found and replaced:

OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25

OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25
NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25

etc., etc.,

I have tried using the following wildcard find/replace:
FIND: 8. August*Aug
REPLACE: 9. September*Aug

But while the find works, the replace just produces a "formula you typed
containes an error" message.

Any pointers? I'm sure this kind of find & replace is possible in Word...

Thanks,
Matt