+ Reply to Thread
Results 1 to 5 of 5

Open variable name workbook & vlookup to find that variable named wookbook

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    30

    Open variable name workbook & vlookup to find that variable named wookbook

    Hello, I am back again with a new challenge (at least challenging for me). Actually, I have a 2 fold problem that I need help solving.

    A. I have a PO report that will be download every Tues & Thurs.
    B. The resulting Excel file will always be archived in the same folder (path is constant).
    C. The naming convention for the Excel files is “PO_Report” + “YYYYMMDD”. Example for today (Thurs the 6th): ...."PO_Report_20140306.xlsx".
    D. The second piece that is missing is a vlookup statement that will find the newly opened workbook (the target sheet will always be “Summary”).

    1. A. If today is Tuesday, I need code to find last Thursday’s report (minus 5 days) and open it. So, basically {Workbooks.Open Filename:="C:\Path/”Previous report.xlsx”}
    B. If today is Thursday, find last Tuesday’s report (minus 2 days) and open it.

    2. Then I need a vlookup from the current report finding data in the newly opened archive report. So, basically…… vlookup(A2,’[newly opened archive.xlsx]Summary’! $C:$G,4,FALSE)

    Again path is constant, file name changes by "today -5" or "today -2" days, and sheet name is constant.

    Please let me know if my description is not clear.
    Any help is greatly appreciated!!! Thank you!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Open variable name workbook & vlookup to find that variable named wookbook

    You should find the newest workbook with a name like PO_ReportYYYYMMDD instead of depending on running the macro on a specific day. Set a reference in your project to Microsoft Scripting Runtime, and use code like this:

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 03-06-2014 at 02:05 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Open variable name workbook & vlookup to find that variable named wookbook

    HI Bernie. Thanks for the help, I really appreciate it. I am sorry... I have no idea what goes where in the code you created I am hoping you can translate down to imbecile level....

    The actual name of the excel file that will be worked on is PO_Download.xlsx
    Today being Thursday, the name of the file needed to be found is PO_Download_20140304.xlsx
    The path to that file is C:\Documents\3 Purchase Order Data Dumps\POs\PO_Download_20140304.xlsx

    I understand that you are telling me what to insert, but I simply do not understand what you are telling me....

    Can you help further?

    Thanks again!!!!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Open variable name workbook & vlookup to find that variable named wookbook

    Try this version - remember, the code will put a formula into cell B2 of the activesheet, using the value from cell A2 as the lookup....post back if you need another cell(s)


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Open variable name workbook & vlookup to find that variable named wookbook

    Thank you!! Your are truly a life saver

+ 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. [SOLVED] VBA - Defining Variable Named Open Workbooks
    By jjremme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-18-2013, 01:21 AM
  2. using a variable named range in a vlookup formula
    By Daniela1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2013, 02:10 PM
  3. Copying Data from a variable-named workbook
    By j.lancaster1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-16-2012, 05:09 PM
  4. variable in to input data into variable named sheet and if does not exist create
    By rwhidden in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-30-2011, 12:08 AM
  5. Code to reference variable named workbook?
    By ajava in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2009, 08:22 PM

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