Pulling data from closed workbook of varying name

[Vizeey]

Hello,
I want to pull information from different files, with names based on input in a cell. I only managed to do this with Workbooks.Open, and I am now looking for a way to pull the information without the need to open the target workbook.
With the help of ExcelGPT I got this function and sub (picture).
I added the variables "wrkID", "ipd", "nummer" and "mtf" as ways to create the path for the desired files.

The script produces no errors, but inputs a #VALUE in the output cell.
I am very new to the use of "Application.Evaluate()", and dont know where the error occurs.
Please let me know of there are any more information needed to solve this issue.

Mathias

 

[saboh12617]

Hello,

As far as I know with VBA/formulas you will need to open the workbook to retrieve data. If you aim to keep them closed, you should use PowerQuery. Especially if you want to import whole tables for example.

Look in DATA > GET DATA > From File > From Excel workbook.