1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by sms2luv, Jul 26, 2017.

  1. sms2luv

    sms2luv Member

    Messages:
    284
    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: Jul 26, 2017
  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    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.
  3. sms2luv

    sms2luv Member

    Messages:
    284
    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.

    Attached Files:

  4. Haseeb A

    Haseeb A Active Member

    Messages:
    364
    Hello,

    INDEX/MATCH combination will work between closed workbook.
  5. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    @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.
  6. sms2luv

    sms2luv Member

    Messages:
    284
    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
  7. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    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.
    Thomas Kuriakose likes this.
  8. sms2luv

    sms2luv Member

    Messages:
    284
    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
  9. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    I assume the network drive is mapped? If so just use whatever the drive letter you assigned to it.
  10. sms2luv

    sms2luv Member

    Messages:
    284
    Due to security reasons
    We cannot see or access any drive
    We have to use the network path only.
  11. sms2luv

    sms2luv Member

    Messages:
    284
    Unfortunately it didn't work.
    Any other workaround.
  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    Version of Excel and License SKU?
  13. sms2luv

    sms2luv Member

    Messages:
    284
    I am using ms office 2007.
    Don't have license SKU as its a corporate version and we cannot check.
  14. sms2luv

    sms2luv Member

    Messages:
    284
    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)
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    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?
  16. sms2luv

    sms2luv Member

    Messages:
    284
    Tried typing on Win Explorer, and internet explorer CTRL+0, didn't work.
    Please provide some alternative
  17. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    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.
  18. sms2luv

    sms2luv Member

    Messages:
    284
    Alright, thanks for help.
  19. sms2luv

    sms2luv Member

    Messages:
    284
    Can you help with vlookup formula
  20. sms2luv

    sms2luv Member

    Messages:
    284
    Please help with Index, Match formula with Loops. For vba
  21. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,894
    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!
    sms2luv likes this.
  22. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  23. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    As I have stated. It's very difficult to help you without knowing all details.

    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.
    sms2luv likes this.
  24. sms2luv

    sms2luv Member

    Messages:
    284
    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 (vb):
     
    Application. Worksheet. Vlookup(,,,,)
     
    This didn't even worked on an open book
  25. sms2luv

    sms2luv Member

    Messages:
    284
    I used this, but didn't work.

    Code (vb):

    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)
     

Share This Page