VBA to read a single value from a closed workbook

Hello excel lovers,

I am trying to solve an age old problem of reading a single value from a closed excel file into my current, open workbook. I realise that the Indirect function doesn't work with closed files and have found the below formula and VBA on the web.

The below formula is useful but static. I would like to be able to pass the folder location, excel filename, sheet reference and cell reference from information contained in my open workbook.

='C:\path\to\your\folder\[Book1.xlsx]Sheet1'!A1

The below VBA

Function GetValueFromClosedWorkbook(filePath As String, sheetName As String, cellAddress As String)
    Dim wb As Workbook
    Set wb = Workbooks.Open(filePath, False, True) ' Open as read-only
    GetValueFromClosedWorkbook = wb.Sheets(sheetName).Range(cellAddress).Value
    wb.Close False ' Close without saving
End Function

and function call in excel, is as close as I can find, but doesn't work for me. With Value error not being easy to resolve.

=GetValueFromClosedWorkbook("C:\path\to\your\folder\Book1.xlsx", "Sheet1", "A1")

Would anyone be able to correct the above or suggest an alternative for me?

When calling this function I am using =GetValueFromClosedWorkbook(B1,B2,B3) where cell references B1, B2, and B3 in my current, open workbook, contain text strings of path (including file name), sheet and cell reference respectively.

Thank you,

Rob

I haven't tested it ( working on an iPad ) but it looks like it should work. The obvious flaw is that it opens the workbook, gets the value and closes the workbook without saving it.

Maybe try this instead: https://www.codevba.com/excel/closed..._get_value.htm

How about

Sub jec()
[c11].Formula = "='" & [D2] & "[" & [D3] & "]" & [D5] & "'!A1"
[c11].Value = [c11].Value
End Sub

or

Sub jec()
[c11] = ExecuteExcel4Macro("'" & [D2] & "[" & [D3] & "]" & [D5] & "'!R1C1")
End Sub

Bump.

I'm not having a lot of success testing the solutions.

Anyone else got any ideas?

You don't need to write multiple lines of code if you set up the references in your worksheet like the first one.
It could be written as a function ofcourse but you can't use the function directly in the worksheet unfortunately

I would like to be able to pass the folder location, excel filename, sheet reference and cell reference

Assuming you have these 4 values above in ranges B1, B2, B3, B4

D:\Steven\Desktop\
FORM.xlsx
Sheet1
A1

Then this as per above will work...For other references in other cells you could add a loop...

Sub J3v16()
Dim Valu, FilePath As String, File As String, Sht As String, Cell As String
FilePath = Range("B1"): File = Range("B2").Value: Sht = Range("B3"): Cell = Range("B4")
Valu = ExecuteExcel4Macro("'" & FilePath & "[" & File & "]" & Sht & "'!" & Range(Cell).Range("A1").Address(, , xlR1C1))
End Sub

You could do it with ADO - something like:

Function GetValueFromClosedWorkbook(filePath As String, sheetName As String, cellAddress As String)
   Const adOpenStatic As Long = 3
   Const adLockReadOnly As Long = 1
   Const adCmdText As Long = 1
   Const ACE_OLEDB_PROVIDER As String = "Microsoft.ACE.OLEDB.12.0"
   Dim cn As Object
   Set cn = CreateObject("ADODB.Connection")
   
   With cn
      .Provider = ACE_OLEDB_PROVIDER
      .ConnectionString = "Data Source=" & filePath & ";" & _
      "Extended Properties=""Excel 12.0 XML;HDR=No;"""
      .Open
   End With
   Dim index As Long
   If Range(cellAddress).Row = Rows.Count Then
      cellAddress = Range(cellAddress).Offset(-1).Resize(2).Address(0, 0)
      index = 1
   Else
      cellAddress = Range(cellAddress).Resize(2).Address(0, 0)
      index = 0
   End If
   Dim Query As String
   Query = "SELECT * FROM [" & sheetName & "$" & cellAddress & "]"
   
   With CreateObject("ADODB.Recordset")
      .Open Query, cn, adOpenStatic, adLockReadOnly, adCmdText
      If Not .EOF Then GetValueFromClosedWorkbook = .getrows()(0, index)
      .Close
   End With
   cn.Close
End Function

Here's a test bed workbook with RomperStomper and jec's solutions demonstrated.

Just note that jec's version will not work with any cell in the last row of the sheet (unlikely, I know )

And thank you for the rep Trevor.

I think something must have changed with the ExecuteExcel4Macro workings these days as I'm sure that code used to work.

I think something must have changed with the ExecuteExcel4Macro workings these days

Works perfectly for me...

Oh of course...Misunderstood...Tx for clarifying JEC...