+ Reply to Thread
Results 1 to 23 of 23

Vlookup from another workbook into thisworkbook

  1. #1
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Vlookup from another workbook into thisworkbook

    Hello everyone,

    I got a code that opens another workbook (and is working as intended), but then I would like to do a vlookup into the start workbook from the workbook that was just opened. In the file that is opened up there is just two columns with data. Row 1 is a header but this is my table_array in a vlookup where the col_index_num is 2 (the second column). My lookup_value is on the other hand from my start workbook where the lookup_value is in column 4 (D). The destination of the result (value) of the vlookupformula is in my start document in column 16 (P). My code is below but I do something wrong with my vlookup formula I think.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    I didn't see anything obvious wrong with your code. If the source sheet name has a space in its tab name, then the formula address would need single quotes around the sheet name.

    Below is your code shortened a bit. The data range address includes the workbook and sheet names.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Thanks, your code is working for me I probably had some small error like you said.

    In my start document there is a lot of data where in column 15 (O) there is a start week. In the second document there is in column 2 (B) also a start week but its more accurate (newer data). Right now in my code I add the more "accurate" week in the column to the right of the previous start week (column 15-16). Is there a better way where I can automatically update column 15 instead of having two separate rows. In other words, if the vlookup finds a week then replace the start week (in column 15) otherwise let the previous start week stay as before. Is that possible in any way?

  4. #4
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Do you know if that is possible AlphaFrog with a ranged vlookup or do I need to loop through a vlookup?

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    Probably have to loop through it, but I don't understand what you described.

  6. #6
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    In short terms, I got a column where there is a date in every cell. If my vlookup function finds a date (a succesfull lookup) then replace the previous date with the lookup value. If my vlookup function don't find a date (#N/A) then do nothing (keep the previous date).

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    Try this (not tested)...

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Thanks AlphaFrog, this working as intended.
    But the thing is that since this is a loop of over 150000 rows the code runs very slow and therefore its better to use your previous code and then just delete the other row. But anyway thanks for taking time and helping me! Have a good Christmas!

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    This should be fast

    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    Disregard the code in post #9

    This should be fast

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    That was amazing!!! The speed of this lines of codes went from around 15-20 seconds to maybe 1 second. Wow!
    I got a macro right now that takes around 30 seconds totally to run (this part is just a bit of it). If you have the time and the will to help me speed up my macro, I would be very grateful. Then I can send a total copy of my excelfile with my macro. But I understand if you rather want to help other threads since you already helped me with this thread.

    Greeting,
    Niclas

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    You're welcome.

    Attach the file and I'll take a look. No guarantee.

  13. #13
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Ok, let me explain. I got my excelfile where all the code is (named here as Example1.xlsb). It goes into my folder and finds a folder called ("Module") and open the big datafile in that folder. I cannot attach the datafile since its greater than the limit on this site but it looks exactly the same as my examplefile with data in several sheets. It collects all the data from that workbook into myworkbook (Example1.xlsb) and close the datafile. Then my macro finds another folder called ("Byggtider") and make a vlookup to adjust the build week (This is the part of the code you have helped me with). It then closes the other workbook, make some filter/sorting and is then finished. Its a bit hard to explain shortly but I hope you understand something atleast. Just ask questions if you don't understand.
    Attached Files Attached Files
    Last edited by gandreso; 12-20-2016 at 11:50 AM.

  14. #14
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    I don't know why the attach file opens in PDF-format for me. Its a excelfile. Maybe I can send it to your mail address?

  15. #15
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    Can't download either file. I get a message; "Invalid Attachment". Don't know what the problem is.

  16. #16
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    I am not giving out my emails address. You could upload it to a file share site and post the links here.

  17. #17
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    When I open the file it doesn't work either, opens in another format?!

  18. #18
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    I managed to get it to work. When open the file it has somehow made itself to a .php format. I save the file in .xlsx and then I can read the file. I don't know why it changes the format, it has never done that before..

  19. #19
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Should I post all the code instead? But then its harder for you to understand.

    Edit: I fixed it. I attach the files in a zipfile, then it should work. Sorry for that, no idea why that happened..
    Attached Files Attached Files
    Last edited by gandreso; 12-20-2016 at 12:07 PM.

  20. #20
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    I got the files. A little busy today. It will take some time.

  21. #21
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    Dont worry! You can look at it when you have time. You have already helped me alot.

  22. #22
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Vlookup from another workbook into thisworkbook

    The most expensive part of your code is the process of opening workbooks. There is a method (ADO) to extract data from closed workbooks. I am not experienced with ADO so I wouldn't be comfortable with coding it for you. Here's a link that may help if you want to try on your own.

    Copy a range from closed workbooks (ADO)

    I don't see any other parts of your existing code that could be made noticeably faster.

  23. #23
    Forum Contributor
    Join Date
    07-07-2014
    Location
    Göteborg
    MS-Off Ver
    2010
    Posts
    130

    Re: Vlookup from another workbook into thisworkbook

    I see, I will check it out for sure! Thanks anyway for all your help. Have a good day Sir.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to access another workbook's ThisWorkbook
    By thatoneguy102 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-23-2016, 04:11 PM
  2. Help Copying/transfering Data from one Workbook to ThisWorkbook
    By Jester07 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2014, 01:55 AM
  3. Show One WorkBook while Not showing ThisWorkBook.
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2014, 12:16 PM
  4. Copying VBA held in 'ThisWorkbook' to a new workbook
    By goodwinh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2011, 01:52 PM
  5. Vlookup from ThisWorkbook
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2010, 10:22 AM
  6. Copy Thisworkbook code into a new workbook
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2009, 03:17 PM
  7. ThisWorkbook.FollowHyperlink to Location in Workbook
    By James Cox in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-25-2005, 11:06 AM

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