Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find filename | Excel Discussion (Misc queries) | |||
set up an index linked to work book tabs | Excel Worksheet Functions | |||
filename search and extract into a cell | Excel Discussion (Misc queries) | |||
Changing Filename when linked to other worksheets | Excel Discussion (Misc queries) | |||
how to extract a filename? | Excel Worksheet Functions |