VLOOKUP from a closed workbook

[ADAMC]

Hello all. Thank you in advance for any help I will try and explain and I hope someone can help.
I have a book called book 1. I would like some VBA in book 1 which will vlookup column A in a closed workbook called Book 2 returning column g of book 2 in column g of book 1.

The only problem is I book 2 is formatted like Book2 DD/MM/YYYY.xlsx and I always need to open and lookup column G from book 2 with the previous days date or today -1.

With some google searches I can do this when the file is the same name but not when the file is today’s date minus 1 each day.

Can anyone help?

Thank you!

 

[Joe4]

The only problem is I book 2 is formatted like Book2 DD/MM/YYYY.xlsx

Are you sure? I don't think that is possible. "/" are not valid characters to be used in file names.

I can do this when the file is the same name but not when the file is today’s date minus 1 each day.

Post the code that you have that works for today's date, and we can show you how to adjust it for yesterday's date.

 

[ADAMC]

Apologies it isn’t / it is underscores like this DD_MM_YYYY.

I don’t have any code at the minute. I do this with a VLOOKUP formula at the moment every day by going and finding the workbook from the previous day.

Open book 1
B2 is a VLOOKUP of A2, book2 previous days date, column G. Then I paste as values and close the workbook from the previous day.

Thanks

 

[Joe4]

So, is the exact format of the name of the file: "Book2 DD_MM_YYYY.xlsx"
Will the prefix ALWAYS be "Book2"?

Can you post an example of one of your hard-coded VLOOKUP formulas?
(I could just give you the piece that figures out the date, but then you would need to figure out the rest)?

That part look would look like:

="Book2 " & SUBSTITUTE(TEXT(TODAY()-1,"mm/dd/yyyy"),"/","_") & ".xlsx"

of course, you need to use the INDIRECT function with it.