McGimpsey & Associates 

Get cell value from a closed workbook

Getting a cell value from a closed workbook using VBA involves opening the file, and reading from the workbook, worksheet and range objects. MVP John Walkenbach published a macro function using an Excel 4 macro statement that gets it much more quickly.

I modified it (very) slightly to be more Mac-friendly, using Application.PathSeparator rather than "\" or ":".

Note that this cannot be called from a worksheet cell. It can, however, be called from a worksheet event macro.

    '*********************************************************
    'Purpose:   Get a value from an open or closed workbook.
    'Inputs:    sPath: Complete path to the workbook's directory
                sFileName: workbook's file name, including extension
                sSheetName: target sheet's tab name
                sCellRef: target cells's address, A1-style
    'Returns:   Variant containing cell value or "filename not found"
    '*********************************************************
    Public Function GetCellValue(sPath As String, _
            sFileName As String, _
            sSheetName As String, _
            sCellRef As String) As Variant
        'Slightly modified from John Walkenbach's routine:
        '    http://j-walk.com/ss/excel/tips/tip82.htm
        'by J.E. McGimpsey
        '    http://mcgimpsey.com/macoffice/excel/getcellvalue.html
        'Retrieves a value from an open or closed workbook
        Dim sArg As String
        Dim sPathSep  As String
        sPathSep = Application.PathSeparator
        If Right(sPath, 1) <> sPathSep Then sPath = sPath & sPathSep
        'Make sure the file exists
        If Dir(sPath & sFileName) = "" Then
            GetCellValue = sfileName & " Not Found"
            Exit Function
        End If
        'Create the argument
        sArg = "'" & sPath & "[" & sFileName & "]" & sSheetName & "'!" & _
            Range(sCellRef)(1).Address(ReferenceStyle:=xlR1C1)
        'Execute an XLM macro
        GetCellValue = ExecuteExcel4Macro(sArg)
    End Function

This page last updated Sunday, 4 January 2004