+ Reply to Thread
Results 1 to 9 of 9

Vlookup() on closed workbook

  1. #1
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Vlookup() on closed workbook

    The data worksheets for my analysis are HUGE. The file itself takes about 3 minutes to open. So I am trying to use Vlookup()s on a closed workbook.

    Here is the code:

    Please Login or Register  to view this content.
    I am getting a type mismatch error when compiling the project.

  2. #2
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Lightbulb Re: Vlookup() on closed workbook

    Hi lloydgodin,

    Im not sure this would work but an attempt to resolve your query with a simple formula.

    In any newly open workbook try performing the below Vlookup - with your search value in A2.

    Please Login or Register  to view this content.
    I was just unsure of the sheet name in the closed workbook. So please replace the red text with the sheet name and try.


    Thanks,

    Bonny Tycoon



    **If I was able to help please click the small star icon at the bottom left of my post **

  3. #3
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Vlookup() on closed workbook

    Looking to do this in VBA and not within the cell. Thank you for your suggestion.

  4. #4
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Vlookup() on closed workbook

    I have too less knowledge on VBA; however, found someting different within your codes. The red colored dont match.


    Please Login or Register  to view this content.
    Can you try matching them and lets see if it works? Fingers crossed


    Thanks,

    Bonny Tycoon



    **If I was able to help please click the small star icon at the bottom left of my post **

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Vlookup() on closed workbook

    what you are doing won't work. if you don't use a formula in the cell you'll need to open the workbook or use an excel4 macro. why not put the formula in the cell and then replace it with its value?
    or since you only want a small table for your lookup just use direct link formulas to extract the table to the current workbook and then use that in the vlookup.

  6. #6
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Vlookup() on closed workbook

    you only want a small table for your lookup
    The tables are immense. As an example 1 array is D278605:F284285. There are over 523 per worksheet. There are 8 worksheets.

    use an excel4 macro
    This sounds promising, but I have no clue what it is. Someone else suggested XML shema. Again, no clue.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Vlookup() on closed workbook

    why not put the formula in the cell and then replace it with its value?
    +1 .
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Vlookup() on closed workbook

    I don't know what XML schemas have to do with it but I reckon your options are (in pretty much the order I'd recommend)
    -formula, then convert to value as suggested previously
    -use a database program
    -querytable to extract the data you want into the current workbook
    -ado to grab the table data

  9. #9
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Vlookup() on closed workbook

    Not Resolvable At This Time.

+ 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