Display filename/tab of a linked spreadsheet

[mswenoflb]

Hello, first time poster to the forum. We want to display the filename/tab of the linked spreadsheet that provides the data in an adjacent cell. We can do it by linking to a cell with file name / tab, but we would like to do it by looking at the linked formula and pulling the filename / tab from it. Any ideas?


Thanks,


Mark

 

[Justin]

Hi,

Try this

[pre]

Function ExternalLinkAsString(cell As Range, _
Optional default_value As Variant)
ExternalLinkAsString = CStr(cell.Range("A1").FormulaLocal)

End Function

[/pre]

It simply gets the formula of any selected Cell and displays it as a string.

To extract pieces of the string to get File Name and Path and workbook Sheet Names etc, etc try looking in to the search, left, right etc text formulas in Excel.

 

[mswenoflb]

Thanks Justin, I will give it a try.


Mark

 

[ThrottleWorks]

Awesome !

 

[shajan]

Hi, where do we enter this, in a module ?

Thanks.

 

[Luke M]

shajan,


Yes, all UDF's need to be put in a VB module. Then you can call them either from within another macro, or the excel workbook (if applicable).

 

[shajan]

Thanks Luke, Will try ! quite useful.