Pull data from a closed workbook

Extracting data from a closed file in another workbook is a common request by most of the excel user. They would like to pull or consolidate data from closed files; however, this is not possible. There is a workaround which helps to solve this problem. The solution is using Macro code.


Question: On a daily basis, I spend time in copying data from one file to another. There are two files “Open.xls” & “Closed.xls” & I want to copy data from “Closed.xls” to “Open.xls” via VBA code.


Key things to know:

  • The first thing is we should know the “Usedrange” of the closed workbook i.e. “Closed.xls” within the open workbook for e.g. “Open.xls”
  • We can use IF function in Usedrange of “Closed.xls” workbook within the “Open.xls” workbook & it will extract the data from “Closed.xls” workbook
  • If the referencing cell is blank, #N/A is put in place. Using the specialcells method, you can then delete all the #N/A errors and change the formula to values


To start with it, we will save two excel files 1) Open.xls 2) Closed.xls in path “D:\Test Folder”

Following is the snapshot of “Closed.xls” workbook:




To automatically save data from “Closed.xls” file to “Open.xls” file, we need to follow the below steps to launch VB editor

  • Click on Developer tab
  • From Code group, select Visual Basic




  • Copy the below code in ThisWorkbook (Closed.xls)


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

'Put in the UsedRange Address of Sheet1 Closed.xls (this workbook)' --- Comment
    Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address

End Sub




  • To pull the data in “Open.xls”, copy the following code in the Standard Module


Sub Importdata()
Dim AreaAddress As String
Sheet1.Cells(1, 1) = "= 'D:\Test Folder\" & "[Closed.xls]Sheet2'!RC"
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
     .FormulaR1C1 = "=IF('D:\Test Folder\" & "[Closed.xls]Sheet1'!RC="""",NA(),'D:\Test Folder\" & _
      On Error Resume Next
      .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
      On Error GoTo 0
      .Value = .Value

End With
End Sub




  • Copy the following code in ThisWorkbook (Open.xls)


Private Sub Workbook_Open()

Run "Importdata"

End Sub




Now the VBA codes are all set; all we need to do is open the file name “Open.xls”. Following is the snapshot of “Open.xls” file:




The code is not limited to cell copy from A1.usedrange; the code will pick the starting range & will work perfectly fine. The files are available for download & we recommend you to have a go.


Conclusion: With VBA code, we can automate data extraction task without opening destination workbook & we save ourselves from doing manual copying & pasting.


rar icon


If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

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


Users are saying about us...

  1. Is there no way to connect several working workbooks to a single workbook DataFile which has several sheets of data) and is not usually open when referencing to the single DataFile. The DataFile has a new version(like “2015-1 or “2016-1″ once new prices are entered. It worked fine until Indirect was used to bring up the right version of DataFile. This is required as when Data Costs change we do not want to have old files changing and there are up to 2000 formulas on the working file which would be very time consuming as there several sheets and not all under each other. Your help would be much appreciated.

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