vlookup from closed workbook , but files are in same folder

[hudson andrew]

Hi all,

I was wondering if there is way we can really do a vlookup against closed work for example.

because today i did a vlookup in 10/25/2017 with 10/26/2017 tomorrow i do it with 10/26/2017 and 10/27/2017. likewise so on .

path is same but the file dates are different . currently i am using below code to run but i am looking for some dynamic way code that can help my things easy.

Please Login or Register  to view this content.

[Special-K]

Try changing this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

where A1 is the cell you have stored the date in

[shg]

A filename can't contain slashes.

[hudson andrew]

Hello K.

I just need a fix can you please help me on this . I am using tables FYI . below is the formula

=VLOOKUP([Job Opening ID (Job Opening)],'Z:\Hudson\ Reporting\andrew\Sample\Nov 2017\Copy of NIC closed jobs-2nd November 2017 - hudson.xlsx'!Open[#Data],49,0)

and how can i refer this in the code . can you help please

[Special-K]

I have no way to test this properly but try

Range("a3") = "VLOOKUP([Job Opening ID (Job Opening)],'Z:\Hudson\ Reporting\andrew\Sample\" & Format(Range("A1"), "mmmm") & " " & Format(Range("A1"), "yyyy") & "\Copy of NIC closed jobs-" & Format(Range("A1"), "dd") & "& LOOKUP(DAY(A1),{1,2,3,4},{""st"",""nd"",""rd"",""th""}) " & Format(Range("A1"), "mmmm") & " " & Format(Range("A1"), "yyyy") & " - hudson.xlsx'!Open[#Data],49,0)"

You will get a problem because the bit highlighted in red will return 2 digit days, and you've implied you have oth 1 and 2 digit days. Not sure how to resolve this.

[hudson andrew]

Hi K,

Thanks for your time , it really worked for me .
i have another challenge assume now my destination file is protected and i know the password. every time i run the macro it is asking me to give the password just because my source file is protected .

how do we fix this , any inputs please ?.

[Special-K]

Sorry, am no VBA expert.
VBA and passwords is beyond me.