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”.

dataFile

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.

ArrowAfterRunningMacro

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube