find & replace link

[Guest]

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Dave Peterson]

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Guest]

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Dave Peterson]

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Guest]

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Dave Peterson]

Not that I know of...but did you try edit|links.

You get to point and click at the new file--it might be safer/easier.

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

:

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Guest]

Is a long process to do edit|link. Sound very lazy of me, but I guess it the
only way to get the work done.

Thanks.

Not that I know of...but did you try edit|links.

You get to point and click at the new file--it might be safer/easier.

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

SOS wrote:

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

:

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Guest]

Hi, Dave,

found a solution.
1. all link formula preceded by =, so I find & replace with #, with this
link disconnected
2. then I find \A and replace by \B, find DataA and replace by DataB
3. lastly find # and replace with =, to re-establish the link.

After pulling hair for past 1 week, finally found ways to get the work done
in a more efficient manner. Thanks

Is a long process to do edit|link. Sound very lazy of me, but I guess it the
only way to get the work done.

Thanks.

Not that I know of...but did you try edit|links.

You get to point and click at the new file--it might be safer/easier.

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

:

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

SOS wrote:

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

:

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Dave Peterson]

Ahhhh.

I thought you could the whole string at once.

I see the problem and I understand your solution.

But I'd be a little more careful. I'd change = to ##########
(so I'd know that it would never be used in any cell)

Hi, Dave,

found a solution.
1. all link formula preceded by =, so I find & replace with #, with this
link disconnected
2. then I find \A and replace by \B, find DataA and replace by DataB
3. lastly find # and replace with =, to re-establish the link.

After pulling hair for past 1 week, finally found ways to get the work done
in a more efficient manner. Thanks

Is a long process to do edit|link. Sound very lazy of me, but I guess it the
only way to get the work done.

Thanks.

Not that I know of...but did you try edit|links.

You get to point and click at the new file--it might be safer/easier.

SOS wrote:

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

:

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

SOS wrote:

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

:

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks

 

[Guest]

Thanks, will do as per your advise.

Ahhhh.

I thought you could the whole string at once.

I see the problem and I understand your solution.

But I'd be a little more careful. I'd change = to ##########
(so I'd know that it would never be used in any cell)

Hi, Dave,

found a solution.
1. all link formula preceded by =, so I find & replace with #, with this
link disconnected
2. then I find \A and replace by \B, find DataA and replace by DataB
3. lastly find # and replace with =, to re-establish the link.

After pulling hair for past 1 week, finally found ways to get the work done
in a more efficient manner. Thanks

Is a long process to do edit|link. Sound very lazy of me, but I guess it the
only way to get the work done.

Thanks.


:

Not that I know of...but did you try edit|links.

You get to point and click at the new file--it might be safer/easier.

SOS wrote:

Yes, I have verified the worksheet names.

Is there anyway I can temporarily disable the prompt forlocation of the
files when I perform Find & Replace? At least I can change all As to Bs
faster, save the file & reopen to see whether the link is correct. By then I
can do change source if the link is incorrect.

:

If excel doesn't think the file is there, I bet it's not there.

And you did verify the worksheet names, too, right?

SOS wrote:

The workbooks are there. Is just they are located in various folders:

\Data\B\1\[DataB1.xls]!..., folder B\2, B\3, etc.

Basically, I want to copy Sheet 1 (those link to As folders) to Sheet 2. For
Sheet 2, I want the link to Bs folders. Edit|Link|change source would change
Sheet 1 as well, which is not what I want.

Alternatively, I thought of using the INDIRECT function, but this requires
the link files to be opened (approx 200 files). Any other formula which
doesn't requires link file to be opened?

:

That means that the "new" workbook/worksheet doesn't exist. So either be a
better typist <bg> or only do a few until you're positive you got it correct.

And maybe Edit|Links|change source would be easier/safer.


SOS wrote:

In a worksheet, I have 15,000 links to data files as follows:

'C:\Data\A\1\[DataA1]
'C:\Data\A\2\[DataA2] and so on.....

I would like to replace them as follows:

'C:\Data\B\1\[DataB1]
'C:\Data\B\2\[DataB2] and so on ....

As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !. Pls help. Thanks