Find & Replace a string contained in a link
 

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

Find & Replace a string contained in a link
 

How about just using
Edit|links|Change sources.

Matt from GVA wrote:

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

Find & Replace a string contained in a link
 

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



"Dave Peterson" wrote:

How about just using
Edit|links|Change sources.

Matt from GVA wrote:

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

Find & Replace a string contained in a link
 

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.

Matt from GVA wrote:

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

"Dave Peterson" wrote:

How about just using
Edit|links|Change sources.

Matt from GVA wrote:

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


--

Dave Peterson