Linked workbooks - variable wb path.

[Tone]

Hi all,

I am working on a project which will use linked workbooks.

Ok, what I would like to do is have a 'configuration sheet' where I
could include some variables like the path for certain linked
workbooks.
In Book1, Cell A1 = \\network_path\
In Book1, Cell A2 <-- here I would like to know if there is a function
(way to do it either in Excel or VBA) to 'build' the cell reference to
the external workbook.

I tried = INDIRECT(A1&"[workbook]the_sheet_name'!Cell_reference") but
that does not seem to work - I also tried other variations but no
luck...

Basicaly I just want to be able to change one cell (A1) to change all
links i the workbook.

Any sujestions?

Thanks,
Tony

 

[Earl Kiosterud]

Tony,

This one has been bandied quite a bit. You're not alone. INDIRECT is
perfect for this application. Except it doesn't work on closed workbooks.
As I said, INDIRECT is useless for this application.

If you're into macros, building the linked cells from your table is probably
the only way. It can be made completely automatic. Post back if you're
interested and willing. We can give you some code.

Consider carefully if all the data should be in one workbook. Often
separate sheets and separate workbooks are used in a project which make
difficult or impossible to use Excel functionality.

 

[Harlan Grove]

Tone wrote...
....

Ok, what I would like to do is have a 'configuration sheet' where I
could include some variables like the path for certain linked
workbooks.
In Book1, Cell A1 = \\network_path\
In Book1, Cell A2 <-- here I would like to know if there is a function
(way to do it either in Excel or VBA) to 'build' the cell reference to
the external workbook.

I tried = INDIRECT(A1&"[workbook]the_sheet_name'!Cell_reference") but
that does not seem to work - I also tried other variations but no
luck...

Basicaly I just want to be able to change one cell (A1) to change all
links i the workbook.

....

In short, Excel make this quite difficult. INDIRECT won't work when the
references are to closed workbooks. The reason is syntactic: INDIRECT
only returns range references, but references into closed workbooks are
always returned as scalars or arrays, not ranges.

The following link discusses the known work-arounds.

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?F2993260A ). If the data you're
fetching comes from tables with column headings, the SQL.REQUEST
approach would probably be best.

Actually, another alternative would be using template link references
like

='<PATH>[filename.xls]worksheet'!$X$99

which will evaluate as errors, then use the Workbook_Open event to call
Edit > Replace, and replace <PATH> with the drive/directory path you're
fetching into cell A1.

 

[Harlan Grove]

Earl Kiosterud wrote...
....

Consider carefully if all the data should be in one workbook. Often
separate sheets and separate workbooks are used in a project which make
difficult or impossible to use Excel functionality.

....

The logical extreme of this is cramming everything into a single
worksheet, and that often leads to poor, almost unmaintainable designs
as well. Multiple worksheets are often needed for clean designs, so
learning to deal with them is a necessity (and not a particularly
onerous one).

As for multiple workbooks, Excel has poor tools. Data > Consolidate is
good for aggregating calculations across several workbooks in the same
drive/directory, but it can't perform nonaggregation extracts.
Unfortunately, this is One of the areas in which Excel in 2005 is still
miles behind Lotus 123 back in 1989.

 

[Tone]

Thax guys,

How about a user defined function that will take an argument (the path)
or various arguments (path, workbook name, sheet name)?

i.e.:

A1 = "path" - B1 = "workbook" - C1 = "sheet" - D1 = "cell"

=get_source(A1, B1, C1, D1)

Do you guys have a sample function that I can adapt?

Thanks,
Tony

 

[Harlan Grove]

Tone wrote...

How about a user defined function that will take an argument (the path)
or various arguments (path, workbook name, sheet name)?

....

Did you read my other response in this thread?

 

[Tone]

Did you read my other response in this thread?

Yes Harlan, thanks.

I used your pull function, which does the trick.

However I might need the SQL.REQUEST but the addin does not seem to
work on Excel 2003 - Is there are trick to make it work is an easy
work around?

Thanks,
Tony

 

[Harlan Grove]

Tone wrote...
....

However I might need the SQL.REQUEST but the addin does not seem to
work on Excel 2003 - Is there are trick to make it work is an easy
work around?

....

If by add-in you mean Laurent Longre's MOREFUNC.XLL and its
INDIRECT.EXT function, you'll need to ask Longre himself. He doesn't
seem to read the newsgroups much any more, so you'd need to use his own
forum to ask him for advice.

http://xcell05.free.fr/forums/viewforum.php?id=4

Helps if you know a little French, but only a little, and even if you
don't it shouldn't be too difficult to figure out.

 

[Tone]

No! The Addin I mean is the one for Excel 2002 (*XLODBC.XLA*), which
doesn not seem to be supported for Excel 2003

Any work arounds?!

 

[Harlan Grove]

Tone wrote...

No! The Addin I mean is the one for Excel 2002 (*XLODBC.XLA*), which
doesn not seem to be supported for Excel 2003

....

The Excel 2002 add-in works under Excel 2003. You can download the
Excel 2002 add-in from Microsoft's web site (you'll need to search on
the filename), then just use Tools > Add-Ins in Excel 2003 to load it.