Excel cell reference from closed workbook using file name in cell

Question

I have a central location that contains 96 xls files; each of these 96 files represent an individual test located here:

"\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME"

I created an Excel workbook to input all the information of each individual test on to a single sheet. Then, I used a VBA to pull the names of the files and add a link to the file on a second sheet on the same workbook. I linked those files to sheet 1.

Next, I added all the information on the top row corresponding to the test performed broken down in to a single line per workbook (Test).

Now I need to populate sheet 1 on the new workbook (Advanced Main board test log.xlsx)

So far this formula works, but only for open workbooks:

=INDIRECT("'\\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\["&A7&"]Summary'!$E9")

Where A7 represents the column of filenames which ranges from A6 to A103

Summary E9 is the cell I would like to enter in cell C7

Excel capture of C7

Please note the populated values, this is because I have those 2 work books open, All #REF! cells contain the same formula as the populated cells.

please let me know if you need any additional information to accomplish this tedious task

I also tried doing Microsoft recommended method:

='[20160613 1002257 35000010-01B.xls]Summary'!$E9

replaced file name with cell containing file name:

='["&A7&"]Summary'!$E9

and I get #REF! in the cell and formula bar automatically changes to:

='(\\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\["&A7&"]Summary'!$E9

so far this formula works but only for open workbooks - if you open the closed workbooks, does it work for them then? — Scott Holtzman
yes it does work only when open but as soon as you close it and the workbook refreshes the values disappear — Allan

Kevin Kevin · Accepted Answer · 2016-09-07T20:23:57

According to Microsoft, the Indirect() function must have the workbook opened. You could cycle through the directory with VBA to get the cell values using this route.

Got a UDF and below is how you'd add it:

Open Visual Basic editor in excel (Development tab of the Ribbon - you may need to go into Excel options to unhide it).
Right click VBA Project([Your workbookname]), highlight insert and then click module.
Paste the below in module1 and save the workbook as a .xlsm.

Code:

Function GetField(Path As String, WorksheetName As String, CellRange As String) As Variant

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range

    Set wb = GetObject(Path)
    Set ws = wb.Worksheets(WorksheetName)
    Set rng = ws.Range(CellRange)

    Application.DisplayAlerts = False
    wb.Saved = True            
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Function

You can then use =GetField() in this workbook only.

Parameters: =getfield("\server5\Operations\MainBoard testing central location DO NOT REMOVE or RENAME\"&A6,"Summary","E9")

Only tested with one file, but you should be able to use cell references, filenames, etc in rows/columns for more than one.

-Confirmed this works on multiple Rows/Columns

Edit: Changed how workbooks are closed from some code provided by Alistair Weir in another issue.

Excel cell reference from closed workbook using file name in cell

1 Answers