Using the Address Function

[Space Elf]

I need to get data from several workbooks so I often use the
"INDIRECT(ADDRESS" function and reference a variable in a cell for the
worksheet name.

Now I am trying to access workbooks in other directories. I have tried
everyway I can think of to get the full path of the workbook into a variable
that will work. So far, no luck. Here is what I want to access:

'C:\Documents and Settings\All Users\Documents\# PROJECT MANAGER\! Current
Projects\1 P652 FNC\[Update.xls]To Updates'!$A$2

I need last folder (1 P652 FNC) to be a varible that the ADDRESS function
will accept.

=INDIRECT(ADDRESS(2,1,,,F1))
The value in F1 is:
"'C:\SharedDocs\# PROJECT MANAGER\! Current Projects\"&F2"&"\[Update.xls]To
Updates'!"))
The value of F2 is:
1 P652 FNC
but that value will change to another folder as needed in the same path,
Workbook & Worksheet names.

Help on Excel 2000?

Thanks,

 

[Clarity]

Hi,

You may find this link useful:

http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm

I have used the User Defined Function in some of my spreadsheets and it
works very well.

Here is the code (nb this just pulls the file name but it shuold be easy to
adapt it to what you need:

Function FunctionGetFileName(FullPath As String)

'Function to get the filename from the full path. Taken from
'http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm

Dim StrFind As String

Do Until Left(StrFind, 1) = "\"

iCount = iCount + 1

StrFind = Right(FullPath, iCount)

If iCount = Len(FullPath) Then Exit Do

Loop


FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)

End Function

Myles