+ Reply to Thread
Results 1 to 13 of 13

Lookup in closed workbook

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Lookup in closed workbook

    I want to lookup data in a closed workbook, that is located on my desktop, without having to open that workbook. I have been trying code as follows, but having problems with the syntax. Any assistance is appreciated. Thanks.
    Please Login or Register  to view this content.
    Last edited by maacmaac; 01-24-2010 at 04:12 PM. Reason: wrong description in title

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: code to lookup data in closed workbook

    Hi, I think the problem is this bit

    ... Sheets("RepNames").Range("A:B") ...

    Pre 2007 Vlookup cannot use whole columns as the lookup range, so you need to specify the rows, too

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    The code works ok if the sheet being referenced is within the same workbook. I just can't get it to work if I am trying to reference a closed workbook. Thanks
    Please Login or Register  to view this content.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Lookup in closed workbook

    You're right. I'm wrong. Vlookup can use whole columns (it was SUMPRODUCT that can't in 2003).

    But some further research yielded this:

    You cannot use VLOOKUP in a macro to look in a closed Workbook. Use Code to add the required formula to a cell ( record a macro Entering it to get code) then read back the result and delete it.
    source: http://www.ozgrid.com/forum/showthread.php?t=60116

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    Thanks for the suggestion but I can't enter the formula in a cell. I need to tackle this with code because I am replacing the value in column H which is also the lookup value.

    For example: in cell H2, the value is 12345. In another workbook (closed) I have two columns. In column A is the lookup value, and column B is the value to return (a name for example).

    Open Workbook:
    Cell H2 = 12345

    Closed Workbook:
    Cell A55 = 12345
    Cell B55 = Jim Anderson

    So value in H2 changes from "12345" to "Jim Anderson"

    Hopefully this makes sense. If not, I can attach an example. Thanks.

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Lookup in closed workbook

    I think the idea here is to use a helper cell to put the Vlookup formula in, evaluate that cell and write the value of the helper cell back into the cell where you want the result to appear. Then you can clear the helper cell.

    I can't write this in VBA, but in pseudo code it would be something like:

    Please Login or Register  to view this content.
    does that make sense?

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Lookup in closed workbook

    Why do you need VBA to do this?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  8. #8
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    I think I need to use VBA to do this. I have attached an example. The first workbook "OpenBook" contains rep codes in column A. The second workbook "ClosedBook" contains the lookup table.

    In the "OpenBook", I need to replace the rep number with the rep name in column A. I couldn't think of a formula that would do this without using VBA. Thanks.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    I've been searching other posts for information about retreiving information in workbooks that are closed. From what I have found, it appears that you can't access data from a closed workbook via VBA code. Any suggestions on how I should tackle this? Not sure if it is possible to just use an excel formula or if I would need some type of code that would open the closed workbook, use the information needed and then close again. Any help is appreciated.

  10. #10
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Lookup in closed workbook

    so what's wrong with the ozgrid suggestion? Why not use a temporary cell to put the formula in, evaluate it, grab the result and clear the cell?

  11. #11
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    I looked at the comments from Ozgrid and can't figure out how it is possible. For example, in column A I have a list of rep numbers. Cell A2 has a value of 12345. In cell A2, I need to replace the number with a name. The lookup table is in another workbook. In the other workbook, rep code 12345 corresponds with the name of John Smith. In cell A2, replace 12345 with John Smith.

    Am I putting my formula in cell A2? If so then how would I keep the lookup value? Sorry if I am not quite getting it but thanks for all your comments.

  12. #12
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Lookup in closed workbook

    Hi,

    Since you are dealing with closed files, you should be looking into one the following :

    1. the UDF called PULL that Harlan Grove wrote which retrieves the value from a closed workbook.

    2. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/
    It includes =indirect.ext() that may help you.

    3. For large queries, you might want to go into coding an SQL query

    HTH

  13. #13
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Re: Lookup in closed workbook

    Thanks for all the comments, but I couldn't figure out from the comments how to retrieve data with the workbook closed. Ended up having to open the workbook, use data needed, and then closing workbook. Code used as follows:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1