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.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
- 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.
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 firstname.lastname@example.org