Quick value reading from closed workbook

Original by Erlandsen data consulting, 2017 

This summary note was Posted on By reinie
Posted in Excel Tagged

Copy paste the code in a module

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = "" 
wbName = Replace(wbName, "'", "''")
arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" &_
Range(cellRef).Address(True, True, xlR1C1)</code> <code> GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

Exemple:

..
Path= "C:\Program Files\ExcelFiles"
strPath = Path & "\"
strFile = "myFinance.xlsx"
cValue = GetInfoFromClosedFile(strPath, strFile, "2008", "B1")
msgBox(cValue)
  • Dont forget to define the variables)
  • Path should end with a \