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

 
 

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.

  2. The method isn't really much use, I think the point has been missed. how to get data from many files, perhaps a few hundred and pull it into one summary sheet as perhaps one line of data from each. If excel had covered this very simple ask with a proper implementation of the indirect function so that one could build dynamic expressions (such as =[external file]sheet!a1 etc, but you cannot insert any function to choose different files by formula within the square brackets! - useless) there wouldn't be a problem . the above solution fails because :
    1. you do have to open the closed file in the first instance to add the sub routine
    2. If you only have a couple of files, you would simply insert a copy/paste link, You wouldn't use VBA for that!

    • I agree with Tom. This would be a wonderful solution if the external file could be a variable, but I too have found this to be a roadblock. ExcelTip people: do you have a simple solution for this apparent conundrum?
      Thanks

      • So what you're looking for can be done in VBA but is probably better suited for VBS(the standalone version of VBA that you can write outside of a microsoft app, it writes the same way but has more functionality). You would have to obviously either know the paths in advance or write the logic to detect the paths. Either way it's entirely possible but to pull hundreds of files like that would probably take 10 minutes (obviously less than the time it would take a person but not instant). In VBS you would still technically need to open the files but you can have all of this run without you ever visibly seeing any of the process!

Leave a Reply

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

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 Youtube