Copy a range from closed workbook (Local, Network and on the internet)		

Ron de Bruin (last update 1 May 2006)

Go to the Excel tips page

You can find three examples here to get a range out of a closed workbook.
Note: All examples use the GetRange macro.

1: file on the internet (this file exist on my site)
2: file in network folder
3: File in local folder

Other useful pages are :

http://www.rondebruin.nl/ado.htm

http://www.rondebruin.nl/summary2.htm

http://www.j-walk.com/ss/excel/tips/tip82.htm

Note: there are five arguments  in the macro's
1: Path
2: File name
3: Source sheet name
4: Source range
5: Destination sheet/range

Note: There is no error check on this moment for the path and file and sheet name.

Sub File_On_Website()
    Application.ScreenUpdating = False
    On Error Resume Next

    'Call the macro GetRange
    GetRange "http://www.rondebruin.nl/files", "test1.xls", "Sheet1", "A1:B100", _
             Sheets("Sheet1").Range("A1")

    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Sub File_In_Network_Folder()
    Application.ScreenUpdating = False
    On Error Resume Next

    'Call the macro GetRange
    GetRange "\\Jdb\shareddocs", "test2.xls", "Sheet1", "A1:B100", _
             Sheets("Sheet1").Range("A1")

    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Sub File_In_Local_Folder()
    Application.ScreenUpdating = False
    On Error Resume Next

    'Call the macro GetRange
    GetRange "C:\Data", "test3.xls", "Sheet1", "A1:B100", _
             Sheets("Sheet1").Range("A1")

    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Main macro

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
             SourceRange As String, DestRange As Range)

    Dim Start

    'Go to the destination range
    Application.Goto DestRange

    'Resize the DestRange to the same size as the SourceRange
    Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
                                     Range(SourceRange).Columns.Count)

    'Add formula links to the closed file
    With DestRange
        .FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
                        & "'!" & SourceRange

        'Wait
        Start = Timer
        Do While Timer < Start + 2
            DoEvents
        Loop

        'Make values from the formulas
        .Copy
        .PasteSpecial xlPasteValues
        .Cells(1).Select
        Application.CutCopyMode = False
    End With
End Sub