Vlookup to a closed Workbook via Function / Excel Add-In

[DrSKA]

Wondering if anyone could help me out on this one.
I've created the Function below (which I'm using as an Excel Add-in), which looks up a value of the selected cell (SKU) and returns the value it finds in a specific workbook that is stored on a specific location.
Right now this code it is working perfectly as long as I have that workbook open in another screen, but I can't seem to make it work when the workbook is closed.

I'm sure I need to add a reference to the file location somewhere/somehow (C:\Report\Inventory\Inventory.xlsm), but I've tried lots of different things but nothing has been working so far.
Does anybody know how can I make this work without having to open the workbook "Inventory" first?

Function CheckInventory(SKU)

CheckInventory = Application.WorksheetFunction.VLookup(SKU, Workbooks("Inventory.xlsm").Worksheets("Details").Range("$C:$AD"), 15, False)

End Function

 

[awoohaw]

I don't think VLOOKUP works on closed workbooks. But, Power Query does. If you connect that workbook via power query you can access the data when the workbook is closed.

 

[brawnystaff]

Believe awoohaw is correct. However, you could setup your VBA to open the workbook you want to reference and then close it when done.

Set refwbook = Workbooks.Open(ThisWorkbook.Path & "\Reference.xlsx")

refwbook.Activate

...

refwbook.Close

 

[Alex Blakenburg]

Just a clarification. You can use a VLookup on a closed workbook in the spreadsheet but as far as I can ascertain not in VBA.
Since you are trying to create a function anyway and have access to MS 365 why not use a Lambda function ?
This format worked for me:
(you can do it in one step but I found this easier)

1) Create a named range as follows (you can change the names)
Named Range: GetData
RefersTo: ='C:\Report\Inventory\[Inventory.xlsm]Details'!$C:$AD

2) Create a second named range being your function:
Named Range: LookUpSKU
RefersTo: =LAMBDA(sku,VLOOKUP(sku,GetData,15,FALSE))

Usage
=LookUpSKU(A2)

 

[DrSKA]

Thanks I appreciate the replies!
It looks like the Lambda function is indeed what I'm looking for, I'll give it a try.

 

[DrSKA]

Just a clarification. You can use a VLookup on a closed workbook in the spreadsheet but as far as I can ascertain not in VBA.
Since you are trying to create a function anyway and have access to MS 365 why not use a Lambda function ?
This format worked for me:
(you can do it in one step but I found this easier)

1) Create a named range as follows (you can change the names)
Named Range: GetData
RefersTo: ='C:\Report\Inventory\[Inventory.xlsm]Details'!$C:$AD

2) Create a second named range being your function:
Named Range: LookUpSKU
RefersTo: =LAMBDA(sku,VLOOKUP(sku,GetData,15,FALSE))

Usage
=LookUpSKU(A2)

This is almost perfect, it does work very well, but the lambda is stored only in the specific workbook you've created them in. Is there a way to make it functional across all workbooks? (similar to what the Function does)

 

[Alex Blakenburg]

Typically a VBA function is stored in a workbook. You could store it in a personal workbook but then it would only work for you.
How were you planning on making the macro available to all workbooks ?
In the same way you were going to do that you could write a macro that added the Lambda function into the workbook you want to use it in.

 

[DrSKA]

Typically a VBA function is stored in a workbook. You could store it in a personal workbook but then it would only work for you.
How were you planning on making the macro available to all workbooks ?
In the same way you were going to do that you could write a macro that added the Lambda function into the workbook you want to use it in.

Hi Alex, You can save the VBA Function as an Add-In and if you activate the add-in under the "Excel Add-Ins", it'll be active across any workbook that you open.
Indeed I do need to store it locally, but it'll automatically load it in whenever I open an Excel file (any file). (and it only needs to work for me)
It works well, just not with vlookups apparently

I can write a normal macro to do the same (I mean not as a function/Add-in), but I will lose the ability of providing an input/range of where the sku is located that it needs to vlookup.
Since I work with a lot of various request/files from different people, where the data is always in a different format, I thought being able to to provide an input to there the sku is located would be a nice feature. It was more about exploring the Function/Add-in functionality than anything else, just a QoL kinda thing that would make my daily work just a tiny bit easier and faster.
Thanks for your help anyway!

 

[Alex Blakenburg]

Sorry I couldn't be of more help.
You can use a macro to add the External Range & Formula to the Name Manager if that is a viable option for you.