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

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

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

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

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

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