-
-
-
-
1 Attachment(s)
Some notes in support of this main forum post
https://eileenslounge.com/viewtopic.php?f=27&t=39859
_____ Workbook: ctry_cd masteList.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
|---|
1 |
Country |
Company |
Country name |
Logistics |
Finance |
|
2 |
229 |
ABC |
Algeria |
01 |
5123 |
|
3 |
229 |
UPS |
Algeria |
08 |
1552 |
|
4 |
373 |
IBM |
Mauritius offshore |
01 |
5153 |
|
5 |
382 |
Toshiba |
Mali |
01 |
5115 |
|
6 |
383 |
Apple |
Equatorial Guinea |
01 |
0833 |
|
7 |
602 |
IBM |
Estonia |
10 |
0393 |
|
8 |
602 |
Microsoft |
Estonia |
NG |
Y022 |
|
9 |
610 |
SAP |
ANGOLA |
MC |
5802 |
|
10 |
613 |
GESNEXT |
Argentina |
TG |
1458 |
|
11 |
613 |
IBM |
Argentina |
01 |
0007 |
|
12 |
|
|
|
|
|
|
-
2 Attachment(s)
Some notes in support of this main forum post
https://eileenslounge.com/viewtopic.php?f=27&t=39859
We can get simple data from a closed workbook. There are a few ways. The way shown here is the simplest, but often the most efficient way. I think it comes about because an “Excel file” , as we see it, is actually some combination of files somehow wrapped up in something that most of us know as an excel file.
One of the files has the values of used ranges, and/or something like that combined with a simplified sort of spreadsheet with just values in it. (I am not sure why that is. Maybe something to do with making things run more efficiently, since those files will be used often rather than looking at an entire sheet where lots of cells would typically be empty).
Strange looking string reference to a closed workbook
Microsoft lets us access the data without opening the full “Excel file”. We do that with a strange looking string reference. I don’t know why Microsoft chose to use such a strange syntax, but as Einstein cleverly advised, we should not waste our brain remembering some code that someone else dreamt up. Instead either note it somewhere , document it good and forget about it and/ or come up with a simple way to get it.
Get the awkward syntax for string reference to a closed workbook
Whether by design or accident, there is a simple way to get the awkward syntax.
Take for example the worksheet in an Excel file shown in the last post, https://www.excelfox.com/forum/showt...ll=1#post21203
Now do this
_ Open that workbook, ctry_cd masteList.xlsx
_ Open any other spare workbook
_ In the spare workbook do this
___ Type in any cell _ =
___Now click on the first cell in the worksheet, Sheet , in the workbook, ctry_cd masteList.xlsx
___Hit Enter
_ if you click on/ look at the cell you were typing in, then you should see this in the formula bar,
='[ctry_cd masteList.xlsx]Sheet'!$A$1
, and in the cell you will see the actiual cell value from the first cell in the worksheet, Sheet , in the workbook, ctry_cd masteList.xlsx , which is
Country
_ Now close the workbook, ctry_cd masteList.xlsx
_ Now you should see that strange looking reference mutate to something even more grotesque , ***the exact form will vary depending on where you have that file, ctry_cd masteList.xlsx , stored. This is what it looks like by me
='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerCl sdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!$A$1
_ You should see that it is still working , in other words it is getting you the value from the , now, closed workbook, ctry_cd masteList.xlsx
_ You should copy that reference and paste it somewhere, for example in a comment in the VB Editor
Code:
' ='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!$A$1
( ***Yours will look a bit different. )
In fact, you do already need to change that a bit, - just get rid of the $s
Code:
' ='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!A1
What to do with that reference to get a range of values (from a closed workbook)
You need to understand one of the most basic things about Excel that so far only I do.
A cell reference without the $s is not really a cell reference. It is a vector, and the value, A1 in this case, tells us how far you are from the origin. If you place that vector, ( stretch it, if you like), into the next cell, then Excel will display you B1
In the example file, lets say I want to import that range into a spare worksheet into a spare workbook, SpareWorkbook.xls
So all I need to do is paste that vector into some range of a similar size. The range I want from the worksheet, Sheet , in the workbook, ctry_cd masteList.xlsx is A1:E11
For no particular reason I will choose a spare range of B2:F12
This coding will put the vector in that range, and then convert the reference into the actual values from the closed workbook that the reference gets you in each cell.
(For convenience, if I put that spare workbook in the same folder as the file in which ctry_cd masteList.xlsx is, then I can simplifier the coding a bit, which is what the ThisWorkbook.Path is about)
Code:
' https://www.excelfox.com/forum/showthread.php/2868-Test-Closed-Workbook-Excel-macros-XLM-(Excel-4-Macros)-winhlp32-files?p=21204&viewfull=1#post21204
' https://eileenslounge.com/viewtopic.php?f=27&t=39859
' ='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!$A$1
Sub PutTheVectorInToGetTheValuesFromClosedWorkbook()
Let Range("B2:F12").Value = "='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!A1" ' YOU WILL NEED to change the path to suit where you have the closed file, ctry_cd masteList.xlsx
' Or, if you have the closed file in the same folder as the file from which this macro is run, then you can use the next line
Let Range("B2:F12").Value = "='" & ThisWorkbook.path & "\[ctry_cd masteList.xlsx]Sheet'!A1"
Let Range("B2:F12").Value = Range("B2:F12").Value
End Sub
If you want to test out what I am talking about,
_ Put both attached files in the same folder.
_ Open just SpareWorkbook.xls
_ Run the macro Sub PutTheVectorInToGetTheValuesFromClosedWorkbook() which is in the, now open, workbook , SpareWorkbook.xls, and be amazed.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
-
5 Attachment(s)
Some more notes and observations for this post
https://eileenslounge.com/viewtopic....308967#p308967
Consider the original file, ctry_cd masteList.xlsx
We can make a copy of the original file :
https://i.postimg.cc/cgRb9nB3/Make-c...-xlsx-book.jpgAttachment 4929
We can be a bit naughty and change the extension to .zip
https://i.postimg.cc/23YJq0C7/Change-type-to-zip.jpgAttachment 4930
https://i.postimg.cc/9XwjnkSK/Change-type-to-zip.jpg
Then we can take a look at what is in it.
https://i.postimg.cc/yWTG00d4/Look-inside-the-zip.jpgAttachment 4931
https://i.postimg.cc/5NcMd3W0/Look-inside-the-zip.jpg
For example take a look at the Xl folder
https://i.postimg.cc/4HXFrxWS/Look-inside-the-Xl.jpgAttachment 4932
https://i.postimg.cc/vTnFx1fb/Look-inside-the-Xl.jpg
After looking around a bit we can find the values from used ranges in simple XML files.
https://i.postimg.cc/0MR40fDx/Look-a...-xml-files.jpgAttachment 4933
https://i.postimg.cc/hjfqgZGJ/Look-a...-xml-files.jpg
My guess is that we can get at the value info quite efficiently from those files, just as we can often get at such simple value information by using various methods to get at text files. My guess is that I can treat a XML file as a simple .txt file
My theory is that my simple closed workbook reference may somehow short-circuit / by-pass the various data base complicated methods, and perhaps work better than such various data base complicated methods.
-
Someone who is a vba fucker shouldn't talk about databases and PQ at all without any idea how it works
-
But then nobody should talk about it, because no one knows how it works. Probably no one knows really how VBA works either. We’re all ignorant fuckers most of the time
-
-
