How do I extract a filename from a formula linked to another book
 

In the current workbook I have a formula linked to another spreadsheet. What
I want to do in the current workbook is to extract the filename only into a
column to represent the source of the file linked..

How do I extract a filename from a formula linked to another book
 

Not sure I know what the formula is , but here are a few options
1) can you use the =MID(Cell,start number,num of charactors) options?
2) can you copy then do data/text to columns and you may have ' as a
deliminator?

hope this helps

"camexcel" wrote:

In the current workbook I have a formula linked to another spreadsheet. What
I want to do in the current workbook is to extract the filename only into a
column to represent the source of the file linked..

How do I extract a filename from a formula linked to another book
 

Here's a somewhat convoluted approach...but it works!

You need some VBA code to extract the formula as a text string then you can
extract the filename from that text string.

So, let's assume cell A1 contains this formula:

=SUM('C:\TV\[test1.xls]Sheet1'!$A$1:$A$5)

Create this user defined function:

Function GetFormula(cell_ref As Range) As String
Application.Volatile
If cell_ref.HasFormula Then
GetFormula = cell_ref.Formula
Else
GetFormula = ""
End If
End Function

To install this UDF:

In the file that you want to do this:
Open the VBE editor - ALT F11
Open the Project Explorer - CTRL R
Find your filename in the pane that opens
Right click on the filename and select InsertModule
Copy/paste the above code into the window that opens
Return to Excel - ALT Q

Enter this formula in cell B1:

=LEFT(MID(getformula(A1),FIND("[",getformula(A1))+1,255),FIND("]",MID(getformula(A1),FIND("[",getformula(A1))+1,255))-1)

Based on the sample formula above, the result is: test1.xls

Note: no error checking! Assumes that the referenced cell does in fact
contain a formula with external references. Also, will not work on formulas
with named external references.


--
Biff
Microsoft Excel MVP


"camexcel" wrote in message
...
In the current workbook I have a formula linked to another spreadsheet.
What
I want to do in the current workbook is to extract the filename only into
a
column to represent the source of the file linked..

How do I extract a filename from a formula linked to another book
 

On Mon, 10 Dec 2007 16:53:01 -0800, camexcel
wrote:

In the current workbook I have a formula linked to another spreadsheet. What
I want to do in the current workbook is to extract the filename only into a
column to represent the source of the file linked..


A VBA UDF is the easiest way.

The following will extract one or more file names and return them as an array.
(If there is only one file name, no need to index through the array).

It takes a single cell as the argument; multiple cell ranges will give a REF
error.

If there is no file name in the cell formula, it will return a blank.

To enter, <alt-F11 opens the VBEditor. Ensure your project is highlighted in
the Project Explorer window, then Insert/Module and paste the code below into
the window that opens.

To use it, enter the formula =FN(cell_ref) in some cell.

================================================== ======
Option Explicit
Function FN(rg As Range) As Variant
Dim re As Object, mc As Object
Dim i As Long
Dim Temp()

FN = ""
If rg.Count < 1 Then
FN = CVErr(xlErrRef)
Exit Function
End If

If rg.HasFormula = True Then
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\[([^[]+)]"
If re.test(rg.Formula) = True Then
Set mc = re.Execute(rg.Formula)
ReDim Temp(0 To mc.Count - 1)
For i = 0 To mc.Count - 1
Temp(i) = mc(i).submatches(0)
Next i
FN = Temp
End If
End If
End Function
====================================
--ron

How do I extract a filename from a formula linked to another book
 

How would you deal with named external references?

Name: test1
Refers to: ='C:\TV\[test1.xls]Sheet1'!$A$1:$A$5

Formula: =SUM(test1)


--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Mon, 10 Dec 2007 16:53:01 -0800, camexcel
wrote:

In the current workbook I have a formula linked to another spreadsheet.
What
I want to do in the current workbook is to extract the filename only into
a
column to represent the source of the file linked..


A VBA UDF is the easiest way.

The following will extract one or more file names and return them as an
array.
(If there is only one file name, no need to index through the array).

It takes a single cell as the argument; multiple cell ranges will give a
REF
error.

If there is no file name in the cell formula, it will return a blank.

To enter, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in
the Project Explorer window, then Insert/Module and paste the code below
into
the window that opens.

To use it, enter the formula =FN(cell_ref) in some cell.

================================================== ======
Option Explicit
Function FN(rg As Range) As Variant
Dim re As Object, mc As Object
Dim i As Long
Dim Temp()

FN = ""
If rg.Count < 1 Then
FN = CVErr(xlErrRef)
Exit Function
End If

If rg.HasFormula = True Then
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\[([^[]+)]"
If re.test(rg.Formula) = True Then
Set mc = re.Execute(rg.Formula)
ReDim Temp(0 To mc.Count - 1)
For i = 0 To mc.Count - 1
Temp(i) = mc(i).submatches(0)
Next i
FN = Temp
End If
End If
End Function
====================================
--ron

How do I extract a filename from a formula linked to another book
 

On Tue, 11 Dec 2007 12:45:00 -0500, "T. Valko" wrote:

How would you deal with named external references?

Name: test1
Refers to: ='C:\TV\[test1.xls]Sheet1'!$A$1:$A$5

Formula: =SUM(test1)

Good question. I had not thought about that. As written, the routine will just
return a blank.

In theory, it could be done, though.

1. (The hard part) -- recognize the portion(s) of the formula that is, or might
be, a Name, then put it (them) into an array.
2. Use the RefersTo property.
3. Run each Name.RefersTo through the REGEX to see if it does contain a
reference.
--ron