Look Up Country name from excel sheet without opening the masterlist

[jonnathanjons]

Dear Team,
Hope you are doing well. I would like to know how I can look up country names from a masterlist of country codes and names and other details from it without opening the masterlist . I have once seen a Excel Addin ribbon macro where the master list is hidden somewhere in the custom Ribbon macro area and is looking up from that list. I am unable to open that list because of a password it had..But now I had unistalled that add in from my excel. I am trying to create a similar master list and vba script where I can look up the country names and other details from it. I attached a sample of Master list here..Kindly note the vba script you provide will be running from my personal.xlsb file.

My request is in my working file (xlxs file) where I will have the country codes in Column A for 1000 rows downloaded from db2 exported to excel,. I would like to have the country name populated in the Column B ( Column name is only an example Ctry name should be poluated based on country code value on the left side column to it by looking up from the masterlist. It should be populated for all the values available in column A in the working file. Can you please help me with this request.

Another request but I will wait until the above is working.Just giving a heads up.
My another request would be to populate the Logistics number and Finance department number based on Country cd + Company name . see the example in masterlist attached. for country code 229 where Logistics number is different for the same country code.so for Logistics details the vba macro should consider the Country name and Company name values in the left side and populate the logistic value from the master List.

Please let me know if I should explain more .i attached a Master file here

[DocAElstein]

Hi, I don’t completely understand you, for example, I don’t know what a xlxs file is, and I am not sure what this means ...... downloaded from db2 exported to excel

But I can perhaps give you something to get some ideas from to get a start. In simple terms I am demonstrating how to get some values from a closed workbook.

A simple demo:
_Download both the files I have uploaded, - put them in the same folder.
_ Only open, SpareWorkbook.xls
_ Run this macro, Sub PutTheVectorInToGetTheValuesFromClosedWorkbook() which is in the, now open, workbook , SpareWorkbook.xls, and be amazed.

Code: Select all

 Option Explicit

'   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
'   ='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!A1
Sub PutTheVectorInToGetTheValuesFromClosedWorkbook()
' Let Range("B2:F12").Value = "='F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\[ctry_cd masteList.xlsx]Sheet'!A1"
 Let Range("B2:F12").Value = "='" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!A1"
 Let Range("B2:F12").Value = Range("B2:F12").Value
End Sub

Assuming that demo works, there are a few more notes about it here


Alan

[snb]

You can store the table in a separate sheet in personal.xlsb.
You can create a function F_get(y) in a macrocodule 'functions' in personal.xlsb that retrieves the desired value in the table in the separate worksheet in personal.xlsb.
You can call the result in any open workbook using:

Code: Select all

MsgBox Application.Run("personal.xlsb!functions.F_get", 2341)

2341 is the argument y for Function F_get

[DocAElstein]

Hello
I am still not sure if the requirement was/ is to get full column data, or pick out some of it.
Previously I showed a way to get a lot of the data from the closed workbook in one go.


Anyway, just as another contribution, here is another idea for if we are wanting to do something along the lines of getting a specific country name based on a given code in column A in this thing, the look up table in the closed workbook, as given in the file, ctry_cd masteList.xlsx, in the first post .

LookUpTable.JPG

I assume for doing this that I have some valid code, ( I will use below as example 602 arbritrarily ), and so I want to somehow use the look up table in the closed workbook, as given in the file in the first post . Preferably without opening that workbook, ctry_cd masteList.xlsx

We can do something based partly on a simplified version of what I did before, that is to say just getting a single value from one cell ( instead of the full range as I did before), in a closed workbook, but after doing something else rather curious and interesting that I just saw..
…Some posts around here look interesting. http://www.eileenslounge.com/viewtopic. ... 29#p287729
(That guy seems to have suddenly stopped posting. Maybe something drastic happened in his life. Some sort of genius by the looks of it, but I feel some strange affinity with him…. )

Example to demo what I am thinking just now :
In the look up table in the closed workbook, as given in the file in the first post we have a country name of Estonia in the third column which relates to a Country number in the first column of 602
So if I have a value of 602, then I can apply some of the ideas there and produce this

Code: Select all

 Sub Ex4Mcro()  '    http://www.eileenslounge.com/viewtopic.php?p=308925#p308925   https://www.excelfox.com/forum/showthread.php/2690-Test-Excel-macros-XLM-(Excel-4-Macros)
Dim r As String, Ex, BK As String, myVal As Long
' Let r = Range("A1:A11").Address(RowAbsolute:=True, Columnabsolute:=True, ReferenceStyle:=xlR1C1, external:=False): Debug.Print r ' R1C1:R11C1
 Let BK = "'" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!"
 Let myVal = 602
 Let Ex = ExecuteExcel4Macro("match(" & Chr(34) & myVal & Chr(34) & "," & BK & "R1C1:R11C1,0)"): Debug.Print Ex '   7
End Sub 

What that does is tells us that we found the value 602 in row 7
So after doing that, we could do a simplified version of what we did before just to get the single Country name from the look up table in the closed workbook,
So the next macro will get just the value from row 7 in the country name list in the third column in the closed workbook table, after doing the last bit to get the 7
The value of Estonia is put arbitrarily in the first cell, using my first idea in my last post, but this time simplified to just get the one cell value.

Code: Select all

 Sub Ex4McroAndPutTheVectorInToGetTheValueFromClosedWorkbook()  '    http://www.eileenslounge.com/viewtopic.php?p=308925#p308925   https://www.excelfox.com/forum/showthread.php/2690-Test-Excel-macros-XLM-(Excel-4-Macros)
Dim r As String, Ex, BK As String, myVal As Long
' Let r = Range("A1:A11").Address(RowAbsolute:=True, Columnabsolute:=True, ReferenceStyle:=xlR1C1, external:=False): Debug.Print r ' R1C1:R11C1
 Let BK = "'" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!"
 Let myVal = 602
 Let Ex = ExecuteExcel4Macro("match(" & Chr(34) & myVal & Chr(34) & "," & BK & "R1C1:R11C1,0)"): Debug.Print Ex '   7
'End Sub
'
'
'Sub PutTheVectorInToGetTheValueFromClosedWorkbook()
 Let Range("A1").Value = "='" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!C" & Ex & ""
 Let Range("A1").Value = Range("A1").Value
End Sub

_._____________________

In fact, because we only want a single cell value from the closed workbook, the ExecuteExcel4Macro( ) thing can be used for that. The syntax is similar to that already discussed for the closed workbook reference, with a couple of minor differences:
_ you miss out the =
_ you must, as always with the ExecuteExcel4Macro( ) thing, use RC type cell references, not the A1 sort.

So this version does away with my first ideas completely

Code: Select all

 Sub Ex4McroAndPutTheVectorInToGetTheValueFromClosedWorkbook()  '    http://www.eileenslounge.com/viewtopic.php?p=308925#p308925   https://www.excelfox.com/forum/showthread.php/2690-Test-Excel-macros-XLM-(Excel-4-Macros)
Dim r As String, Ex, BK As String, myVal As Long
' Let r = Range("A1:A11").Address(RowAbsolute:=True, Columnabsolute:=True, ReferenceStyle:=xlR1C1, external:=False): Debug.Print r ' R1C1:R11C1
 Let BK = "'" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!"
 Let myVal = 602
 Let Ex = ExecuteExcel4Macro("match(" & Chr(34) & myVal & Chr(34) & "," & BK & "R1C1:R11C1,0)"): Debug.Print Ex '   7
'End Sub
'
'
''Sub PutTheVectorInToGetTheValueFromClosedWorkbook()
' Let Range("A1").Value = "='" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!C" & Ex & ""
' Let Range("A1").Value = Range("A1").Value
''
' In fact for just a single value, the  ExecuteExcel4Macro(  )   thing can be used so we can miss out the step of putting that long referrence string in a spare cell
 Let Ex = ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!R" & Ex & "C3")
 MsgBox prompt:=Ex
End Sub 

( It’s a shame that ExecuteExcel4Macro( ) thing cannot return a range of values.
The way I showed originally is quite nice, and often much faster than a lot of the complicated advanced data base analysis manipulation codswallop)

Alan

_.__________________

As before, to demo:
_Download both the files I have uploaded, - put them in the same folder.
_ Only open, SpareWorkbook.xls
_ Run this macro, Sub Ex4McroAndPutTheVectorInToGetTheValueFromClosedWorkbook which is in the, now open, workbook , SpareWorkbook.xls


.

[snb]

@Doc

Why not considering personal.xlsb as an AddIn ?
Why not using Getobject to read from an invisible XL-file ?

[hamster]

very complicated, maybe better to use Power Query

[DocAElstein]

Hi
Why not considering personal.xlsb as an AddIn …. I don’t understand what you mean with that. I never had much to do with Add-Ins. I don’t really know much about Add-Ins. So I don’t really know what you are talking about.

Why not using Getobject to read from an invisible XL-file ? ….. Get object stuff I thought often means something like opening a workbook, but just not being able to see it.
I suspect either
_(i) opening a workbook and doing stuff with it,
or
_(ii) doing something similar with Getobject stuff
are both pretty well the same thing, and involve opening a file. I might be wrong but I thought we were talking about doing stuff without opening a file, which those two things aint, I think, ‘cos they are, opening a file.

Alan

[DocAElstein]

very complicated, maybe better to use Power Query

Hello, Mr hamster, :)
Power Query needs the workbook open, doesn't it?


The first solution suggested, is not very complicated, it is very simple, but never the less has often surprised a lot of people on how quickly and efficiently it works compared to other complicated ways.
That solution is not much more than a single cell reference put in a range of cells simultaneously.
In essence the solution is basically this
Let Range("B2:F12").Value = "='" & ThisWorkbook.Path & "\[ctry_cd masteList.xlsx]Sheet'!A1"
That does it all. Beutifully.
It is very simple, but very beautiful, ... not unlike myself :)

[hamster]

Hello, Mr hamster, :)
Power Query needs the workbook open, doesn't it?

Power Query will work with closed workbook if necessary

[DocAElstein]

Power Query will work with closed workbook if necessary

That is interesting, I didn’t know that.
But I am not too surprised.

I think, ( a Layman guess ), PQ is similar in some respects to other data base analysis things. So it often is involved with values – “number crunching”

If we take a closer look at an excel file , we see it is made up of other files. Some of these files are simpler files, and these files are not much different to simple text files.
My guess is that my simple reference way, and PQ, and all the other data base codswallop, often access the simpler files containing the simpler values.

My way may possibly be the best for simply bringing in a range of values from a closed workbook. It’s simple, brilliant, like me, and has often been overlooked by people a lot smarter.
When it come to manipulating the data that may be another story. My way will then need to manipulate the imported data with standard spreadsheet and VBA ways, which may not always be so efficient. I expect that things like PQ were made to get and manipulate data. So when it is required to get and manipulate data from a closed workbook it may be better. It will depend on the specific requirement. My practical experience is not so great yet. In a few years when I am rich and famous as one of the best windows/ Office/ Computer people, I will get one of my workers to do some extensive test, ... while I am doing something else…

[hamster]

sorry but I must to say: your publications are one big mess without a trace or composition

[DocAElstein]

To get a better composition it has to be bigger, and more use of format and pictures, but that isn’t liked either.
Videos will be more fun perhaps….. :)

[hamster]

sure

[snb]

Don't fool yourself.
You can't use a file without the use of in an interpreter of it's structure.
To get any information in a file it has to be interpreted. Not everything you can't see isn't happening: every method that reads information in a file has to 'open' the file. The only thing is: if you open it in Excel much more will be read and the screen will be adapted accordingly; that isn't the case with GetObject, Powerquery.

[DocAElstein]

How about this, just a suggestion, I don’t know for sure what is really happening….

every method that reads information in a file has to 'open' the a file , or files. The only thing is: if you 'open' it in Excel , much more will be read, (in other words, more files will be 'open', perhaps all of them in the zip package ) , and the screen will be adapted accordingly; that isn't the case with GetObject ** Powerquery and what Alan showed, as that only opens one , or some of the, XML files.
**(As GetObject allows us to do most , (perhaps all?), what we can do with an 'open visible' Excel file, then I am assuming it does almost as much as 'opening' Excel, in other words it opens all or most of the files in the zip package .

… maybe someone who knows for sure, if there is anyone, can clarify it all for us

[jonnathanjons]

Hello Friends. Apologize for not getting back earlier as I had a family emergency and couldn't resume work earlier. Thanks to all who contributed.
I wanted to share small exe file with you which I was referring earlier. but I am unable to attach it here as it shows file too large but its only 315 kb. I am asking to review this file which is shared to me by my colleague .It has a country code and names from where i pull the names. but I'm sure its getting its source from a xls file in the addins but there is no need to open that file while pullin the data.Unfortunately I am not able to open that source file( password protected) so cannot modify country lists..its an old data... There are other wonderful scripts in this. I removed this addin as I have copied most of the scripts to personal.xlsb file.Also I think excel is taking time to launch having this addin in.. Kindly peruse at your leisure.pls let me know how is the script pulling the ctry names from the list without opening it. saw 1 comment says we cannot.

https://drive.google.com/file/d/11UCvgE ... sp=sharing

I am reviewing each comment and trying to see the best option I can use. Thanks again to all.

[rory]

If you use GetObject then yes it does open the program associated with the file.

[DocAElstein]

so does that mean doing stuff with it would likely take about the same time as when 'opening' normally, in other words it pretty well 'opens' everything but you just don't see it?

[DocAElstein]

Hi jonnathanjons,
Hope the family is OK
_.________________________________________

I wanted to share small exe file with you which I was referring earlier

I am not sure I feel like running an exe file that I am not familiar with. If it is producing some infomation about a workbook you want to get at, then could you pass us that info after you run the exe file?
_._______________________________________________

From reading again your first post, I don't see any referring from you to an exe file.
With respect, I think you need to explain more clearly, but first read through carefully all that has been done for you, and maybe before that read from the start what you have writtten. I suspect you may be getting ahead of yourself, and assuming we know things that you know which you have not yet told us...
_.___________________________________________________

I showed you clearly in my first post, that I could get all the infomation from the master file you uploaded in the first post, without opening that master workbook. I demoed it clearly.
The second post I did shows a similar way to do a sort of look up on data to get a particular value depending on another value in a different column in the same row, in other words the clasic VLookUp idea, but done with a match to get the row number, which then is used to pick the data from a column

I don't quite fully understand all what you are saying in your last post, and I did not understand all you said in your first post, as I already pointed out in my first reply.

[rory]

so does that mean doing stuff with it would likely take about the same time as when 'opening' normally, in other words it pretty well 'opens' everything but you just don't see it?

It should be a bit quicker since it opens hidden. If the code is being run from the relevant program then there is no additional overhead since it will use the open instance, but used from elsewhere you would have the overhead of starting that program (together with any files it normally opens at startup).