Linked file in same folder but unknown path

[Guest]

I have a workbook that will gather data from many other workbooks via links.
the other workbooks will always be in the same folder as the workbook
processing the links but I will not know the path of that folder.

I am putting the links in the cells via code like:

Range("b3").Formula = "='[62881.xls]Sheet1'!$B$4"

There are thousands of these links and when I run the code as is, it wants
me to point to the file for each line. How can I set the path to always be
in the current folder, wherever that folder is located?

Thanks in advance for any help
Christy

 

[Guest]

At the beginning of the code you can use the CurDir command to set the
current directory. That is if they are all in the same directory.

 

[Tom Ogilvy]

Why not just use the ChangeLink method.


It is equivalent to going into Edit=>Links, and changing the source of the
link.

however, if the workbooks are in the same folder, you shouldn't need to do
anything. Excel should do what you ask by default (as long as the existing
workbook link isn't valid).

 

[Guest]

Thanks JNW

I tried that but I don't where to put the CurDir? The link is a cell formula
put in the cell via code.

The code puts a formula linking to the other workbook in a cell. The
procedure is ran on opening the workbook. When I open the file and chose
'update links' an "Update values" windows looking in MyDocuments opens
wanting me to point to the file. This will repeat thousands of times for each
line of code.

I need the links to update without any input from the user.

Christy

At the beginning of the code you can use the CurDir command to set the
current directory. That is if they are all in the same directory.

I have a workbook that will gather data from many other workbooks via links.
the other workbooks will always be in the same folder as the workbook
processing the links but I will not know the path of that folder.

I am putting the links in the cells via code like:

Range("b3").Formula = "='[62881.xls]Sheet1'!$B$4"

There are thousands of these links and when I run the code as is, it wants
me to point to the file for each line. How can I set the path to always be
in the current folder, wherever that folder is located?

Thanks in advance for any help
Christy

 

[Guest]

Correction - the 'update value' window appears as the code is placing the
formulas in the cells. When I click 'enable macros' the window apprears for
each line of code.

???????????

Thanks JNW

I tried that but I don't where to put the CurDir? The link is a cell formula
put in the cell via code.

The code puts a formula linking to the other workbook in a cell. The
procedure is ran on opening the workbook. When I open the file and chose
'update links' an "Update values" windows looking in MyDocuments opens
wanting me to point to the file. This will repeat thousands of times for each
line of code.

I need the links to update without any input from the user.

Christy

At the beginning of the code you can use the CurDir command to set the
current directory. That is if they are all in the same directory.

I have a workbook that will gather data from many other workbooks via links.
the other workbooks will always be in the same folder as the workbook
processing the links but I will not know the path of that folder.

I am putting the links in the cells via code like:

Range("b3").Formula = "='[62881.xls]Sheet1'!$B$4"

There are thousands of these links and when I run the code as is, it wants
me to point to the file for each line. How can I set the path to always be
in the current folder, wherever that folder is located?

Thanks in advance for any help
Christy

 

[Tom Ogilvy]

If the source workbook is open you shouldn't have that problem.

--
regards,
Tom Ogilvy

Correction - the 'update value' window appears as the code is placing the
formulas in the cells. When I click 'enable macros' the window apprears for
each line of code.

???????????

Thanks JNW

I tried that but I don't where to put the CurDir? The link is a cell formula
put in the cell via code.

The code puts a formula linking to the other workbook in a cell. The
procedure is ran on opening the workbook. When I open the file and chose
'update links' an "Update values" windows looking in MyDocuments opens
wanting me to point to the file. This will repeat thousands of times for each
line of code.

I need the links to update without any input from the user.

Christy

At the beginning of the code you can use the CurDir command to set the
current directory. That is if they are all in the same directory.

:

I have a workbook that will gather data from many other workbooks via links.
the other workbooks will always be in the same folder as the workbook
processing the links but I will not know the path of that folder.

I am putting the links in the cells via code like:

Range("b3").Formula = "='[62881.xls]Sheet1'!$B$4"

There are thousands of these links and when I run the code as is, it wants
me to point to the file for each line. How can I set the path to always be
in the current folder, wherever that folder is located?

Thanks in advance for any help
Christy

 

[Guest]

Thanks Tom, opening the file eliminated the problem.

Christy

If the source workbook is open you shouldn't have that problem.

--
regards,
Tom Ogilvy

Correction - the 'update value' window appears as the code is placing the
formulas in the cells. When I click 'enable macros' the window apprears for
each line of code.

???????????

Thanks JNW

I tried that but I don't where to put the CurDir? The link is a cell formula
put in the cell via code.

The code puts a formula linking to the other workbook in a cell. The
procedure is ran on opening the workbook. When I open the file and chose
'update links' an "Update values" windows looking in MyDocuments opens
wanting me to point to the file. This will repeat thousands of times for each
line of code.

I need the links to update without any input from the user.

Christy



:

At the beginning of the code you can use the CurDir command to set the
current directory. That is if they are all in the same directory.

:

I have a workbook that will gather data from many other workbooks via links.
the other workbooks will always be in the same folder as the workbook
processing the links but I will not know the path of that folder.

I am putting the links in the cells via code like:

Range("b3").Formula = "='[62881.xls]Sheet1'!$B$4"

There are thousands of these links and when I run the code as is, it wants
me to point to the file for each line. How can I set the path to always be
in the current folder, wherever that folder is located?

Thanks in advance for any help
Christy