Match from closed workbook
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
That's great. Thanks a lot for this simple and tricky way
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Match from closed workbook
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;
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;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Match from closed workbook
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
Alan
http://www.eileenslounge.com/viewtopic. ... 13#p195465" onclick="window.open(this.href);return false;
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
'
http://www.eileenslounge.com/viewtopic. ... 13#p195465" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks a lot for this tricky way. Thank you very much