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:

 

img1

 

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

 

img2

 

  • 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

 

img3

 

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

 

Sub Importdata()
Dim AreaAddress As String
Sheet1.UsedRange.Clear
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\" & _
     "[Closed.xls]Sheet1'!RC)"
      On Error Resume Next
      .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
      On Error GoTo 0
      .Value = .Value

End With
End Sub

 

img4

 

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

 

Private Sub Workbook_Open()

Run "Importdata"

End Sub

 

img5

 

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:

 

img6

 

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

 
 



2 thoughts on “Pull data from a closed workbook

  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>