Match from closed workbook

[YasserKhalil]

That's great. Thanks a lot for this simple and tricky way

[Doc.AElstein]

Hi,

Just for future reference hers is a new You Tube video relevant to this Thread.
https://www.youtube.com/watch?v=e_tDrx3 ... ubs_digest" onclick="window.open(this.href);return false;


By the way, …. something I forgot to mention when I was here last.. Be careful when sharing Files which utilise closed range references of this nature.
There may be additional information held in the Cache which will be available to the recipient. – You may unintentionally share data which you did not intend to !!!! :
https://www.youtube.com/watch?v=-0cDSt0Io40" onclick="window.open(this.href);return false;

Because .xlsx and .xlsm are actually ,zip files , you can do a .zip trick to get at the “hidden information” in the externalLinkx.xml Files:
https://www.excelforum.com/development- ... ost4492413" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4482591" onclick="window.open(this.href);return false;

[Doc.AElstein]

Hi
The code I share here is very simple. I never thought of doing it before as I was not familiar with the .Parent thing.
I just found this code very useful myself to get a few closed references.
( I was getting a lot of data from a lot of workbooks. The workbooks were big but I only wanted the data from a few cells. I was opening the workbooks and running a code to pull in the data each time. It took a ling time to open the workbooks. got the closed references with this code and then pasted them in. It allowed me to do everything a lot quicker. )

I think using a closed reference is a really good and under used thing. Probably a lot of people don’t use it because of its tricky syntax.

This code is a way to get that reference in the correct syntax.

The code gives you the closed reference you need to get to a range.

To use the code , simply select the range you want to get the closed reference for and then run the code.

You can copy from the immediate window the reference formula to paste into any cell to reference the range. It will work if the File whose range you are referencing is open or closed

Code: Select all

Option Explicit
Sub ClosedWbRef()
Dim strAds As String, strSht_Nme As String, strWb_Nme As String, strWb_Pth
 Let strAds = Selection.Address ' cell addresse
 Let strSht_Nme = Selection.Parent.Name ' Worksheet Name
 Let strWb_Nme = Selection.Parent.Parent.Name ' Workbook Name
 Let strWb_Pth = Selection.Parent.Parent.Path ' Workbook Path
Dim strClsdWbRef As String
 Let strClsdWbRef = "=" & "'" & strWb_Pth & "\" & "[" & strWb_Nme & "]" & strSht_Nme & "'" & "!" & strAds
Debug.Print strClsdWbRef ' From VB Editor  Ctrl+g to get the Immediate window - just copy string from there complete and paste in cell
End Sub
'
'_-
'
'
'
'
'
'
'
'
'
'
'
'    http://www.eileenslounge.com/viewtopic.php?f=30&t=25213#p195465
'    https://www.excelforum.com/excel-programming-vba-macros/1126860-is-it-possible-to-sum-entire-column-with-out-opening-excel-2.html#post4321006
'    ='G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan2016\[ProAktuellex8600x2.xlsm]Sheet1'!$I$17388
'

Alan



http://www.eileenslounge.com/viewtopic. ... 13#p195465" onclick="window.open(this.href);return false;

[YasserKhalil]

Thanks a lot for this tricky way. Thank you very much