Trying to INDIRECT XLOOKUP in closed workbooks. Looking for a VBA solution

[xcfalcon351]

Hi All. I really need some help.
I have a number of workbooks as records and need to generate a master register.

The records are in the following format;

R1002.xlsx
	A	B
5	Item	Details
6	Category	Downline
7	Unit Description	Two Hose Umbilical Reeler 480m
8	Fluid Type
9	Max Pressure	5,000 psi
10	Unit Manufacturer	Trident Australia
11	Unit Manufacturer Part No.
12	Unit Serial No.	TS1010
13	Pump 1 Part No.
14	Pump 1 Serial No.
15	Pump 1 Manufacturer
16	Pump 1 Pressure Ratio
17	Pump 1 Max Pressure
18	Pump 1 Flow Rate (L/min)
19	Pump 1 Last Re-Kit Date
20	Pump 2 Part No.
21	Pump 2 Serial No.
22	Pump 2 Manufacturer
23	Pump 2 Pressure Ratio
24	Pump 2 Max Pressure
25	Pump 2 Flow Rate (L/min)
26	Pump 2 Last Re-Kit Date
27	Flushing Pump Part No.
28	Flushing Pump Serial No.
29	Flushing Pump Manufacturer
30	Flushing Pump Flow Rate (L/min)
31	Flushing Pump Max Pressure
32	Flushing Pump Last Re-Kit Date
33	Circulation Pump Manufacturer
34	Circulation Pump Part No.
35	Circulation Pump Serial No.
36	Circulation Pump Flow Rate (L/min)
37	Circulation Pump Last Re-Kit Date
38	Pressure Filter Part No.
39	Return Filter Part No.
Unit Details

My Master Register looks like;

Master Register.xlsm
	A	B	C	D	E	F	G	H	I	J
6	Unit	Category	Unit Description	Fluid Type	Hydraulic Seal Material	Max Pressure	Unit Manufacturer Part No.	Unit Serial No.	Unit Manufacturer	Daily Rate
8	R1002	Gas Booster	SC GBT 30/75 in stainless steel frame	Gas		15,000 psi		None	SC Hydraulics	$150
9	R1003	Pressure Test Unit	101 series SC pump 195:1 8 litre tank			20,000 psi		1016	Trident Australia	$100
10	R1005	Chart Recorder	Dual Pen Chart Recorder			6,000 psi / 15,000 psi		04.02919.1-01	DE WIT	$40
11	R1009	Pressure Test Unit	Pump			10,000 psi		474-212	Haskel
12	R1011	Pressure Test Unit	101 series SC pump 70:1 8 litre tank			7,000 psi		405036	Staffordshire Hydraulics	$100
13	R1012	Pressure Test Unit	101 series SC pump 280: No Tank			28,000 psi		1030	Trident Australia	$100
Equipment List

Column 'A' of the Master Register contains the name of each individual record as well as a hyperlink to the file.

I am currently using the formula;

=LET(x, XLOOKUP(B$6, INDIRECT("'["&$A8&".xlsx]Unit Details'!$A$6:$A$156"), INDIRECT("'["&$A8&".xlsx]Unit Details'!$B$6:$B$156"), 0, 1), IF(x="", "", x))

filled from B8 to J8 of the Master Register to find the column heading in the target record and return the corresponding information to the master register, however this obviously only works when the target record is open.

The intention was that this single formula could be used across the entire master register, and if additional fields from the record were required in the future, adding the column with a correct heading would integrate the additional information without changing the formula.

There are approximately 1000 individual records so I don't think opening each record one by one every time the Master Register is opened is feasible. I need to be able to look into a closed workbook. I also need this action to happen automatically when the Master Register is opened.

VBA is new to me and so far I have been unsuccessful in writing code to make this work.
I did find this code that supposedly did what I needed, but I couldn't work out how to make it work on an array.

Public Function GetValueFromClosedWorkbook(FileName As String, Sheet As String, CellAddress As String)
'?GetValueFromClosedWorkbook("C:\temp\excel partners.xlsx", "Excel", "B2")
Dim strFilePath As String, strFileNameShort As String, strArg As String
    strFilePath = Left(FileName, InStrRev(FileName, "\"))
    strFileNameShort = Right(FileName, Len(FileName) - InStrRev(FileName, "\"))
    strArg = "'" & strFilePath & "[" & strFileNameShort & "]" & Sheet & "'!" & _
          Range(CellAddress).Range("A1").Address(, , xlR1C1)
    GetValueFromClosedWorkbook = ExecuteExcel4Macro(strArg)
End Function

If anyone would take the time to assist me I would be deeply grateful and am happy to provide any additional information.

 

[xcfalcon351]

I could really use a hand with this if there is anyone out there.

 

[xcfalcon351]

Does anyone know of a paid resource for commissioning a script such as I am requesting above?

 

[Fluff]

There are some consultants you could contact here Consulting Services