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:
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
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
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
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
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.