Variable File Reference and Variable Table Array in VBA VLOOKUP

Example: I have a folder "C:\2011\" containing 3 files:
"Record_03 Nov.xlsx"
"Record_12 Apr.xlsx"
"Record_25 May.xlsx"
In these files above, there is only one sheet named "Summary"

In my current active sheet, i need a vlookup function in cell Q2. Table Array (Column P:Q) is from the file with latest date (in file name) in folder "C:\2011\". And the number of rows in this table array is variable for each file.

I manage to write a code to find the file name with latest date. But I couldn't set the vlookup to reference that file.

Error message: "Application-defined or Object-defined Error"

Any ideas???

Please Login or Register  to view this content.

Ok, PROBLEM SOLVED! Key is to set whole file name as variable, without the path in that same variable. Anyway code is as below:

In this code, it will open the old file that is being referenced too. And the workbook in which the vlookup formula should be is "Your_Current_New_Workbook.xlsx"

Please Login or Register  to view this content.