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.
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
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.
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!
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.
Hi David,
Your seems to be a complex query. You can visit our website Excel Forum and opt for professional help from our experts by giving a small nominal fee.
Happy Learning,
Site Admin