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 \