Getting Data From A Closed Workbook

[Ark68]

I've Googled to a point where I've onl;y got myself more confused. I couldn't find an answer that I was able to easily relate to, so here I am.

I have a workbook (WM2) with simple data in it. Three columns, the first being dates, the second the sunset time for that date, and the third being the sunset offset time (sunset - 30 minutes).
In my application, I need to get the value of both the sunset (sunset) and sunset offset (sunsetoffset) times of the date being worked with (inq_date) in my application (WB1)

Is there a way I can use an application.worksheetfunction.vlookup statement in my code referencing the data in the closed workbook (WB2) without having to open it?

for example, where workbooks("WB1") is closed.

sunset = application.worksheetfunction.vlookup(inq_date,workbooks("WB1").worksheets("Sheet 1").range("A:C"),2,false)
sunsetoffset = application.worksheetfunction.vlookup(inq_date,workbooks("WB1").worksheets("Sheet 1").range("A:C"),3,false)

 

[footoo]

One way is to write the formulas to two blank cells, store the results in the variables, clear the formulas.

 

[Ark68]

So, I tried this ...

 With ws_master
                .Unprotect
                .Range("T1") = "=VLookup(inq_date, ('D:\WSOP 2020\SupportData\[sunset.xlsx]sunset'!R1C1:R275C4), 2, False)"
                .Range("U1") = "=VLookup(inq_date, ('D:\WSOP 2020\SupportData\[sunset.xlsx]sunset'!R1C1:R275C4), 3, False)"
                .Protect
            End With
            mbevents = True
     End With

This results with values of '#name' being returned in cells T1 and U1.

What might be the issue?

 

[footoo]

The syntax of your formulas is incorrect. Suggest you use the macro recorder to get the correct syntax.

 

[Ark68]

Thanks Footoo ... to use the macro recorder, I would havbe to open the workbook, which I don't want to do. Won't that give me different results than if I was querying data from a closed workbook?

 

[Larry Haydn]

I saved the WM2 workbook as C:\TEST\WM2.xlsx

Here is the vLookup formula:

Left side: WMS.xlsx Right side: application
Use the IFERROR function to prevent the application from raising an error if the date (column A) is blank.
With this, you can AutoFill the formula down without error.

The formula:
In cell B2:
=IFERROR(VLOOKUP($A2,[WM2.xlsx]SunsetTime!$A:$C,2,FALSE),"")

In cell C2:
=IFERROR(VLOOKUP($A2,[WM2.xlsx]SunsetTime!$A:$C,3,FALSE),"")

 

[Larry Haydn]

These times are the current sunset times in Toronto.

 

[footoo]

Thanks Footoo ... to use the macro recorder, I would havbe to open the workbook, which I don't want to do. Won't that give me different results than if I was querying data from a closed workbook?

Open the source workbook, enter the formula and make sure it is working, close the source workbook, use the macro recorder to get the formula syntax - voila!

 

[Ark68]

Voila indeed! Thank you kind folk for getting me through this. Much easier than I expected, but I'm guessing I can't use a vba variable as an argument. I think that may have been where my issue was.