Different approaches to retrieve data from underlying workbooks
One of the more frequent questions I see on different public forums is “How can I summarize data from several workbooks into a master worksheet?” In general the original posters have made a basic structure, which include fixed folder(s) and the same layout for all the underlying workbooks.
Open Read Close workbooks
This is the basic approach to just simple open each workbook and pick up the wanted information as the following example shows:
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim stFile As String
Dim lnCounter As Long
Sub Open_Read_Close_Workbooks()
Set wsTarget = ActiveWorkbook.Worksheets(1)
stFile = Dir(stDir & “*.xls”)
Application.ScreenUpdating = False
Do While stFile <> “”
Application.Workbooks.Open Filename:=stFile, ReadOnly:=True
Set wsSource = ActiveWorkbook.Worksheets(1)
With wsTarget
lnCounter = .Cells(.Rows.Count, “A”).End(xlUp).Row + 1
.Cells(lnCounter, 1).Value = wsSource.Range(“A2”).Value
End With
ActiveWorkbook.Close
stFile = Dir
Loop
End Sub
Read workbooks using Application.ExecuteExcel4Macro
This approach can be both flexible and fast. The following example shows how it can be applied:
Dim stSource As String
Set wsTarget = ActiveWorkbook.Worksheets(1)
stFile = Dir(stDir & “*.xls”)
Do While stFile <> “”
stSource = “‘” & stDir & “[“ & stFile & “]Sheet1′!R2C1:R2C1”
With wsTarget
lnCounter = .Cells(.Rows.Count, “A”).End(xlUp).Row + 1
.Cells(lnCounter, 1).Value = Application.ExecuteExcel4Macro(stSource)
End With
stFile = Dir
Loop
End Sub
Read workbooks using ADO & SQL
In order to use this approach we need to involve an external library, Microsoft ActiveX Data Object Library, where the version is 2.5 and later.
Dim rst As Object
Dim stCon As String
Dim stSQL As String
Set rst = CreateObject(“ADODB.Recordset”)
Set wsTarget = ActiveWorkbook.Worksheets(1)
stFile = Dir(stDir & “*.xls”)
Do While stFile <> “”
stCon = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & stDir & “” & stFile & “;” & _
“Extended Properties=’Excel 8.0;HDR=No'”
stSQL = “SELECT * From [Sheet1$A2:A2]”
rst.Open stSQL, stCon, 3, 3, 1
With wsTarget
lnCounter = .Cells(.Rows.Count, “A”).End(xlUp).Row
.Cells(lnCounter, 1).CopyFromRecordset rst
End With
stSQL = Empty
stCon = Empty
rst.Close
stFile = Dir
Loop
Set rst = Nothing
End Sub
VSTO 2005 solution
Just for fun I include a solution based on ExcelExecute4Macro:
‘Option Strict On is used in this example and it forbid us to use late binding.
Const stDir As String = “c:DDESources”
Dim stSource, stFile As String
Dim iCounter As Integer
stFile = Dir(stDir & “*.xls”)
Do While stFile <> “”
stSource = “‘” & stDir & “[“ & stFile & “]Sheet1′!R2C1:R2C1”
With Me
iCounter = .Rows.CurrentRegion.Count + 1
.Cells(iCounter, 1) = Globals _
.ThisWorkbook.Application. _
ExecuteExcel4Macro(stSource)
End With
stFile = Dir()
Loop
End Sub
To sum up
Personally I prefer the ExcecuteExcel4Macro approach as it is fast, create no overhead and don’t require opening workbooks. However there are situation where we need to manipulate properties of worksheets and therefore need to open workbooks.
The negative with ExcecuteExcel4Macro is that it can be little bit messy like the following snippet code shows:
.Cells(1 + i, 1).Offset(0, j).Value = _
Application.ExecuteExcel4Macro(“VLOOKUP(““” & _
vaObjects(i, 1) & “”“,” & stFilename & “)”)
End With
When to use ADO/SQL instead of ExecuteExcel4Macro? One possible situation is when we have larger amount of data to be “pumped” to a master sheet.
Anyway, what do You use and what are the scenarios where You apply them?
Edit note:
Ron de Bruin has some good and a more flexible ADO-approach then the above at the following place:
Copy a range from closed workbooks(ADO)
Kind regards,
Dennis
Great stuff these past few days! Thanks to all the writers, this is a great blog. I just started learning VBA unfortunately but I think I’m getting there with the help of this site.
Hi Dennis
Maybe this page on my site is also useful
http://www.rondebruin.nl/ado.htm
Ron,
Yes – Thanks for reminding me :)
I recalled that “someone” had some ADO-samples but I didn’t associated it with You in the first place (propably due to Your great work with Excel & Outlook ;) )
It also reflect another issue, all aspects of Excel is more or less already well known in the global Excel community.
Kind regards,
Dennis
Hi Dennis
Most of the times I open the workbooks.
You have much more control and time to drink Coffee
Examples here
http://www.rondebruin.nl/copy3.htm
Ron,
Well, no wonder why employees prefer that approach while external consultant would like to use it but the customer is not prepared to pay for the ehum coffee-time ;)
Now I wait to see Your link to Your ExecuteExcel4Macro page as well :)
Kind regards,
Dennis
I’ve used two methods. J-Walk’s tip #82 at
http://j-walk.com/ss/excel/tips/tip82.htm
another version of the Excel 4 macro. Very handy for reading in data and populating re-demensioned arrays. I first set a variable that gets the count of the data from a calculation inside the closed workbook, and then re-dim to that variable and populate arrays in a loop, reading from the closed workbook.
When I want to bring in large amounts of data from a closed book, I use ADO as found at:
http://erlandsendata.no/english/index.php?d=envbadacimportwbado
Need to understand that if not getting from the first worksheet, you have to have a named range to retrieve from, per the comments at the top of the web-page.
Both methods work extremely well.
…mrt
Hi Dennis
>Now I wait to see Your link to Your >ExecuteExcel4Macro page as well
I only use it here to test the sheet name Dennis
http://www.rondebruin.nl/summary2.htm
The ADO approach can be rationalized with the following approach:
SELECT data_col
FROM [Excel 8.0;DATABASE=C:db1.xls;].[Test$]
UNION ALL
SELECT data_col
FROM [Excel 8.0;DATABASE=C:db2.xls;].[Test$]
UNION ALL
SELECT data_col
FROM [Excel 8.0;DATABASE=C:db3.xls;].[Test$]
UNION ALL
SELECT data_col
FROM [Excel 8.0;DATABASE=C:db4.xls;].[Test$]
In other words, you can compile a single SQL query and get all the data in one hit. Instead of UNION ALL you could use UNION and eliminate some duplicates along the way. Now the question is, which limit will you hit first: the maximum UNIONs in a Jet query for my approach or the maximum row limit in Excel for yours ;)?
Jamie.
Jamie,
That’s quite impressive and it works excellent – thanks.
In order to answer Your question I believe that it will be a third option (!) – my ability to keep tracks when creating it ;)
Kind regards,
Dennis
Hi Dennis,
Just for the record, if you need to reference a particular cell or range from another workbook you could also do it without using VBA.
For example:
Retrieve a cell value from another workbook:
=’D:SomeDir[OtherWKB.xls]Sheet1′!A1
Or to sum up a range in another workbook:
=SUM(‘D:SomeDir[OtherWKB.xls]Sheet1’!$A$1:$A$9)
These formula’s will work fine even if the source workbook is closed.
To my knowledge not all functions can be used on a closed workbooks though. For example the OFFSET function will only work on an opened workbook. So using a dynamic range in your sum (that uses the OFFSET function) won’t work on a closed workbook.
Rembo
Remco,
I know but since I dislike all kind of links I gracefully avoid them and it will never be part of any solutions I deliver.
Kind regards,
Dennis
Another bit of information about the ExecuteExcel4Macro solution:
In my experience, this solution is not able to import data from sourcerows above 16384. It will return errors in stead of the expected content.
Another bit of information on the ADO solution:
When a column you want to import has got mixed types (alternating between text and numbers) and it is starting with numbers, it does leave the cells in which you would expect the text EMPTY.
At least… that’s my experience (maybe I make some mistake?)
Kind regards,
Marcel Kreijne
I’m affraid I already found the cause of the empty textcells in another subject: External Data – Mixed Data Types:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/#comments
I found that the limit of 16375 (not 16384) rows is not caused by the ExecuteExcel4Macro itself. It is a limit to the rownumber that can be linked to in a closed workbook. At least, I cannot build a link to a cell above row 16375 in a closed workbook, without getting an error…
Using ado or similar seems to pull the data very well, but what about obtaining the whole sheet and its characteristics.
I would be interested to see a vba solution to importing a worksheet from a closed workbook that would include the original worksheet name and all the worksheet attributes.
Hi
Very helpful indeed. I was wondering though, is it possible to get the actual Formula value from a closed workbook, or just the numeric/text value?
Hey Brian,
I’m pretty sure that you cannot. You’d have to open the workbook to do that. You could open a copy of the workbook, for example, if you had to be careful of other processes that might be writing to the workbook at the same time.
But ADO retrieves values only, not formulas.
Btw, if you are the same Brian as here: http://www.xtremevbtalk.com/showthread.php?p=1282370#post1282370, then feel free to continue within the thread if you have more questions…
Mike
Hi
I’m looking for a piece of code whih is checking the file name first before opening and operating on it. File name format is filenameyyyymmdd.xls but the problem is files in that directory are saved irregularly (couple times a week) and I need to retrieve the latest one.
Any ideas?