Option Explicit 
 
 'you can extract data from a closed file by using an
 'XLM macro. Credit for this technique goes to John
 'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm
 
Sub GetDataDemo() 
     
    Dim FilePath$, Row&, Column&, Address$ 
     
     'change constants & FilePath below to suit
     '***************************************
    Const FileName$ = "Book1.xls" 
    Const SheetName$ = "Sheet1" 
    Const NumRows& = 10 
    Const NumColumns& = 10 
    FilePath = ActiveWorkbook.Path & "\" 
     '***************************************
     
    DoEvents 
    Application.ScreenUpdating = False 
    If Dir(FilePath & FileName) = Empty Then 
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" 
        Exit Sub 
    End If 
    For Row = 1 To NumRows 
        For Column = 1 To NumColumns 
            Address = Cells(Row, Column).Address 
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) 
            Columns.AutoFit 
        Next Column 
    Next Row 
    ActiveWindow.DisplayZeros = False 
End Sub 
 
 
Private Function GetData(Path, File, Sheet, Address) 
    Dim Data$ 
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _ 
    Range(Address).Range("A1").Address(, , xlR1C1) 
    GetData = ExecuteExcel4Macro(Data) 
End Function