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..

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..
>

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 Insert>Module
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" <(E-Mail Removed)> wrote in message
news:EFEA6668-4B08-40EA-BCF3-(E-Mail Removed)...
> 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..
>

On Mon, 10 Dec 2007 16:53:01 -0800, camexcel
<(E-Mail Removed)> 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 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 10 Dec 2007 16:53:01 -0800, camexcel
> <(E-Mail Removed)> 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

On Tue, 11 Dec 2007 12:45:00 -0500, "T. Valko" <(E-Mail Removed)> 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