• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlook up closed workbook

S

sms2luv

Member
I want to do vlookup without opening the other worksheet, where I refer the data.
I know Vlookup the normal way in Excel, but not sure how to use it in VBA.

The rows can be 10 or 1000 so would appreciate a code for vlookup code with loop
 
Last edited:
No need to loop. To access closed workbook, you'd use ADO and query the data needed (or use MS Query, PowerQuery etc).

Or just use formula.

Either case, upload sample workbook for both the output and source to get better help.
 
Adding 2 files for examples

It might be a possibility that the formula has been removed as I created the file on PC and downloaded it on my android phone
I want to vlookup emp name
But the other book would be closed.
 

Attachments

  • Mapping.xlsx
    6.4 KB · Views: 4
  • Mapping.xlsx
    6.4 KB · Views: 6
  • vlookup.xlsx
    8.5 KB · Views: 6
@sms2luv
So which workbook is looking up which? And what value need to be brought over?

Both VLOOKUP and INDEX/MATCH works on closed workbook. Though INDEX/MATCH is far more flexible.
 
Want values for Emp name on Vlookup.xlsx file
I tried vlookup but did not work when wb was closed.
Please help with index/match formula
 
Huh? Both worked for me. Note that you will need full path to the workbook.
=VLOOKUP(A2,'C:\Users\MyName\Downloads\[Mapping.xlsx]Sheet1'!$A$1:$B$16,2,FALSE)

=INDEX('C:\Users\MyName\Downloads\[Mapping.xlsx]Sheet1'!$B$2:$B$16,MATCH(A2,'C:\Users\MyName\Downloads\[Mapping.xlsx]Sheet1'!$A$2:$A$16,0))

Replace "C:\Users\MyName\Downloads\" part with folder path to your file.
 
Thanks a lot.
The other file is saved on a shared drive.
We access it using \\indel07\Login data\RTM\Filename.xlsx.
Does it make any difference
 
I assume the network drive is mapped? If so just use whatever the drive letter you assigned to it.
 
Due to security reason, we cannot access the drive path.
To open the file we have to follow the below things.

Open Outlook.
Click new mail.
In subject type \\indel07\Login data\RTM
After typing this becomes a hyperlink, we click on it
Then a new folder popups with shared drive location.
I tried using it at Home and vlookup worked for closed book
I think it could be because of shared drive.:)

I also think that if shared drive was accesible for me and I would had mapped it as Z.
If I sent the file to someone else, they might have an issue, if they had mapped the shared drive as(any other letter)
 
It's due to unmapped network path, lacking drive letter assignment.

I'm not sure even if VBA will work....

Try typing in the network path in Windows Explorer. Are you able to see the folder and the content?
 
If you can't access folder from Windows Explorer. I can't help you. Even with VBA, your company's IT policy will likely block it. You will have to open the file manually following your company's process.
 
Hi, sms2luv!

Posting at a thread without adding anything useful, just to bounce it up, doesn't guarantee better probabilities of getting answers to your original question.

I think that people who usually read these forums or who stumbled over your question, receive a very first impression after reading the original post: either they understand and can help or they understand and are not willing to help or they don't understand.

In any of the three situations I believe that no one will be more tempted to get involved in a thread just because the OP insists, and I repeat, without adding any useful information. Even more, I tend to think that they're less tempted to participate.

For your peace of mind I can assure you that there a a group of very well qualified members of Chandoo.org's community that regularly (I'd say daily) read every question posted at any forum. Surely not all of them all comments at every thread, but I'd bet on that many people read daily every post. If they don't participate, it's up to them. They may think that cannot help, they may not be interested in the subject, there are a lot of reasons why... remember that we all do it for free, in our free time, without any obligation.

Please notice that the sum of time available for collaborators has to be distributed between all the questions posted by the community. Sometimes it happens that there are members who abuse on posting/asking/demanding (I'm not saying in any way that could be one them), and that reduces significantly the medium time/dedication resulting for regular non-abusive members.

Please be patient, I recommend you to read this, if you haven't done it yet, which seems you haven't...
http://chandoo.org/forum/threads/new-users-please-read.294/

Where you may find this:
"
Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."
And this:
"
If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."
If that's not suitable for you, of course that you can always hire a consultant and agree with him the terms of the response time."

Personally the comments to bump up non answered threads move me farther away from reading them again. But this is not a general rule, it just happens to me.

Regards!
 
Thanks a lot for the info.
I understood the forums terms and conditions.
I also understand that its a voluntary participation, however they are many threads I made which are unanswered, the reason behind topping up the post was just to let the members know about it.
It will not be repeated in future.
Sorry in case I hurted any of forum member.
 
As I have stated. It's very difficult to help you without knowing all details.

Even with VBA, your company's IT policy will likely block it. You will have to open the file manually following your company's process.
Click to expand...

If file can be accessed directly, then I can definitely help. But without knowing the IT policy or how the network is set up. I'll be shooting in the dark as to how the file can be accessed and I'm not willing to spend time on that.

FYI - What I find with many of your posts that makes me skip it, is that you lack sample file and your question isn't specific enough to help without it. Or question is too generic to give any specific solution.
 
Great news.
I went to admin of my organization as they have drive letters assigned in their system.
I did vlookup from a Closed book which was on a shared drive.
What I mean to say is that, for me drive letters are not visible and I cannot map it
I guess if I use vlookup using Vba it should work.
I searched the web and found
Code: 
Application. Worksheet. Vlookup(,,,,)
This didn't even worked on an open book
 
I used this, but didn't work.

Code: 
Sub lookupcheck ()
Dim myrange as Range
myrange= \\delindp08\Suhas\Look.xlsb.sheet1.Range("A2:B198")
Sheet1.range("T2").value = Application. Worksheet. Vlookup(Sheet1.Range("P2"), myrange, 2, false)
 
You must log in or register to reply here.
Back
Top