Extract Data From Closed Workbooks Data from a closed workbook is duplicated in the open workbook. Excel Extract Data From Closed Workbooks Ease of Use Intermediate Version tested with 2000, 2003 Submitted by: johnske. Description: Data from a closed workbook is duplicated in the open workbook. 2014 VBA Express.
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:UsersRamandeepDesktop”.
Logic explanation
We have created two procedure in this article:-
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:UsersRamandeepDesktop[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
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 [email protected]
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |