Getting Values From A closed workbook using VBA in Microsoft Excel

In this article, we will create a procedure to fetch data from closed workbook to active workbook using VBA code.

Raw data which we want to fetch is present in the range A13:E22 on sheet "Sheet1" in “DataFile.xlsx” file which is placed in the path "C:\Users\Ramandeep\Desktop".

Logic explanation

We have created two procedure in this article:-

1. CallingMainProcedure

2. GetValuesFromAClosedWorkbook

CallingMainProcedure procedure

This procedure is used for calling procedure “GetValuesFromAClosedWorkbook”. It assigns values to parameter of “GetValuesFromAClosedWorkbook” procedure.

GetValuesFromAClosedWorkbook procedure

This procedure takes file path and file name of closed workbook as parameters. It also takes sheet name and range from closed workbook as parameter. Using the input parameter’s value, an array formula is entered to range A13:E22 of the active sheet as

{=’C:\Users\Ramandeep\Desktop\[DataFile.xlsx]Sheet1’!A13:E22}

Array formula brings the required data from closed workbook. Once data is fetched to the active sheet, formulas from the sheet is removed by replacing the formulas with the value.

Please follow below for the code

Option Explicit

Sub CallingMainProcedure()

'Calling GetValuesFromAClosedWorkbook procedure
'Specifying the file path, filename, sheet name and range of data
GetValuesFromAClosedWorkbook "C:\Users\Ramandeep\Desktop", "DataFile.xlsx", _
            "Sheet1", "A13:E22"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
            
With ActiveSheet.Range(cellRange)
    'Assigning the array formula to the specified range
    .FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & cellRange
    'Removing formula from the cell and pasting the values in the cell
    .Value = .Value
End With

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com