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".
We have created two procedure in this article:-
This procedure is used for calling procedure “GetValuesFromAClosedWorkbook”. It assigns values to parameter of “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
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
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.