» Getting Values From A Closed Workbook using VBA in Microsoft Excel

CATEGORY - Files, Workbook, and Worksheets in VBA VERSION - All Microsoft Excel Versions

The following will return the values on a sheet in a closed workbook and write them to the same range on the active worksheet.

Place the code below into the standard module

Sub test()
    GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
            "Sheet1", "A1:K30"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
    With ActiveSheet.Range(cellRange)
        .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                    & sName & "'!" & cellRange
        .Value = .Value
    End With
End Sub