If you ever had to gather data from several Excel files to generate a global report, then you know the time it takes to open all files and gather values from the weorksheets. This code will retrieve a value from a closed Excel workbook using vba.

Option Compare Database
Option Explicit

' ***************************************
' Retrieve a value from a closed Excel workbook from Access
' ***************************************
' Copyleft 2012 De Zinger ICT
' ***************************************
' Instructions:
' Set a reference to then Excel object library
' Set DefaultSheetName to the default sheet name in your current language
' Set AlternativeSheetName to the alternative sheet name in your alternative language
' ***************************************
' Parameters:
' Path: path to file to retrieve value from
' File: file to retrieve value from
' Sheet: sheet to retrieve value from
' R1C1Ref: reference to cell to retrieve value from
' ***************************************
' Usage:
' GetExcelValue("C:\tmp\sample\", "sample.xls", "sheet1", "R1C1")
' ***************************************
Public Function GetExcelValue(Path As String, File As String, Sheet As String, R1C1Ref As String) As Variant
Dim arg As String
Const DefaultSheetName As String = "Blad"
Const AlternativeSheetName As String = "Sheet"

    On Error GoTo ErrHandler
   
    ' Check if file exists
   If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Dir(Path & File) = "" Then
        GetExcelValue = "File Not Found"
        Exit Function
    End If
   
    '   Create the argument
   arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & R1C1Ref
   
    '   Execute an XLM macro
   GetExcelValue = ExecuteExcel4Macro(arg)
    If IsError(GetExcelValue) Then
        ' Handle 2023 error for default sheet name
       If Right(CStr(GetExcelValue), 4) = 2023 Then
            '   Create the argument
           arg = "'" & Path & "[" & File & "]" & Replace(Sheet, DefaultSheetName, AlternativeSheetName) & "'!" & R1C1Ref
            GetExcelValue = ExecuteExcel4Macro(arg)
        End If
    End If
   
Exit Function
ErrHandler:
    MsgBox Err.Number & " " & Err.Description
End Function