How do get a path name from a cell?

F

Fable

Greetings,

I have a macro that needs a file (from an inactive workbook) calle
“data123.xls”; but my users move this file pretty much where ever the
want on their PC.

Is there a way to have the user fill out cell A1 (Sheet1) with the pat
name (e.g. “C:\My Documents\ACME”) and have the macro search for the xl
file according to the cell patch name given by the user?

Appreciate any hel
 
D

Dave Peterson

You'd want to use a function called =indirect() to build that string and return
the value. But the bad news is that =indirect() won't work with a closed
workbook--you get an error.

But Harlan Grove wrote a UDF that allows you to do this.
http://www.google.com/[email protected]

You build a formula that looks like this:

B1=Path
B2=workbook
b3=sheetname
b4=address.
(Your addresses would be different.)

=pull("'"&B1&"["&B2&"]"&B3&"'!"&B4)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top