find & replace link
 

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

find & replace link
 

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

find & replace link
 

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?

"Dave Peterson" wrote:

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

find & replace link
 

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?

"Dave Peterson" wrote:

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


--

Dave Peterson

find & replace link
 

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.



"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson

find & replace link
 

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.

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

find & replace link
 

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.


"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

find & replace link
 

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

"SOS" wrote:

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.


"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

find & replace link
 

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)



SOS wrote:

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

"SOS" wrote:

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.


"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

find & replace link
 

Thanks, will do as per your advise.


"Dave Peterson" wrote:

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)



SOS wrote:

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

"SOS" wrote:

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.


"Dave Peterson" wrote:

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.

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson