I was helping a user in a French forum who wanted to retrieve an Excel WorkBook’s cell/range value and thought that I’d share the code for anyone else looking to do this.

'---------------------------------------------------------------------------------------
' Procedure : Excel_GetRangeVal
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve an Excel Range's cell value
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path & filename of the Excel Workboon to get the value from
' sSht      : Worksheet name to retrieve the value from
' sRangeAdd : Range, B21 (for instance), you wish the get the value of
'
' Usage:
' ~~~~~~
' Excel_GetRangeVal("C:\Users\Monkey\Banana.xlsx", "5 Little Monkeys", "C12")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-06-01              Initial Release -> Forum Help
'---------------------------------------------------------------------------------------
Function Excel_GetRangeVal(ByVal sFile As String, _
                           ByVal sSht As String, _
                           ByVal sRangeAdd As String) As Variant
    Dim oExcel                As Object
    Dim oExcelWrkBk           As Object
    Dim oExcelWrSht           As Object
    Dim bExcelOpened          As Boolean

    'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        Set oExcel = CreateObject("Excel.Application")
    Else
        bExcelOpened = True    'GetObject worked -> Excel was already running
    End If
    On Error GoTo Error_Handler

    oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
    oExcel.ScreenUpdating = False

    Set oExcelWrkBk = oExcel.Workbooks.Open(sFile)    'Open the Workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(sSht)    'Get the right WorkSheet to work with
    Excel_GetRangeVal = oExcelWrSht.Range(sRangeAdd).Value    'Get the Range value we are interested in

    oExcelWrkBk.Close False    'Close the WorkBook without saving now that we're done.

    If bExcelOpened = False Then    'Close excel if is wasn't originally running
        oExcel.Quit
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not oExcelWrSht Is Nothing Then Set oExcelWrSht = Nothing
    If Not oExcelWrkBk Is Nothing Then Set oExcelWrkBk = Nothing
    If Not oExcel Is Nothing Then
        oExcel.ScreenUpdating = True
        oExcel.Visible = True    'Make excel visible to the user
        Set oExcel = Nothing
    End If
    Exit Function

Error_Handler:
    '9      -> can't find the worksheet
    '1004   -> can't find the file
    If Err.Number = 9 Then
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Excel_GetRangeVal" & vbCrLf & _
               "Error Description: Unable to locate the specified WorkSheet '" & sSht & "'" & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        GoTo Error_Handler_Exit
    Else
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: Excel_GetRangeVal" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End If
End Function

If you need to retrieve more than one cell/range then, instead of using this code multiple times, you’d be best to build an array of the values within a single call and return an array (or collections, …) so you only make one call to Excel to open the file.