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
Example
..
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 \