Macro to sum values from various workbooks if they exist

[samchox]

Hi,

Im trying to come up with a Macro that will perform a =SUM() function across multiple workbooks. Some of these workbooks may exist, some may not.

For example, Summary.xls has a =sum() formula that picks up data from wbk1.xls
wbk2.xls
wbk3.xls
etc, all the way to 100

Today i only have wbk 1, 2 and 3 but i know everyday more wbk's are added so i want to create a sum type function to accomodate the sbk's that may be created at a future time.

Right now when i try it, I not only get REF/# but on opening Summary.xls I get a popup that asks me to define the missing wbk's

Any suggestions?

 

[samchox]

I tried setting the ReadOnly:=True parameter in the following macro, but it doesnt work. Im not suer of the correct parameter to use....


Sub OpenAndProcess()
Dim vaFileName As Variant, wbkData As Workbook
Dim vaDataTotal As Variant, vaDataWbk As Variant, lRow As Long, lCol As Long
Const MyDir As String = "C:\My Documents\Test"
'the location of the workbooks

With Application.FileSearch
.NewSearch
.LookIn = MyDir
'the directory to search in
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
'workbooks found
Application.ScreenUpdating = False
ReDim vaDataTotal(1 To 26, 1 To 3) As Variant
For Each vaFileName In .FoundFiles
'loop through each found workbook
Set wbkData = Workbooks.Open(FileName:=vaFileName) , ReadOnly:=True
'open the workbook
With wbkData
vaDataWbk = .Worksheets("Sheet1").Range("A20:C45").Value
'get range data into variant (array)
For lCol = 1 To 3
For lRow = 1 To 26
vaDataTotal(lRow, lCol) = _
vaDataTotal(lRow, lCol) + vaDataWbk(lRow, lCol)
Next lRow
Next lCol
'add current array values to total array values
.Close savechanges:=False
'close without saving
End With
Next vaFileName
ThisWorkbook.Worksheets("Sheet1").Range("A20:C45").Value = vaDataTotal
Application.ScreenUpdating = True
Else
MsgBox "There were no Excel files found."
End If
End With

End Sub

 

[bojanpaun]

Hey everybody,

I've been looking for a macro that could sum a range of data from more the 100 different woorkbooks into one maste workbook, and into defined range.

This is the code that i have been using, that you have posted before, but it seems that doesn't work when there are more then 5 wokrbooks.

-------------------------------------------------------
Sub sumarno()
Dim vaFileName As Variant, wbkData As Workbook
Dim vaDataTotal As Variant, vaDataWbk As Variant, lRow As Long, lCol As Long
Const MyDir As String = "D:\MF"
'the location of the workbooks

With Application.FileSearch
.NewSearch
.LookIn = MyDir
'the directory to search in
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
'workbooks found
Application.ScreenUpdating = False
ReDim vaDataTotal(1 To 253, 1 To 7) As Variant
For Each vaFileName In .FoundFiles
'loop through each found workbook
Set wbkData = Workbooks.Open(Filename:=vaFileName)
'open the workbook
With wbkData
vaDataWbk = .Worksheets("SUMARNO").Range("AC47:AI299").Value
'get range data into variant (array)
For lCol = 1 To 7
For lRow = 1 To 253
vaDataTotal(lRow, lCol) = _
vaDataTotal(lRow, lCol) + vaDataWbk(lRow, lCol)
Next lRow
Next lCol
'add current array values to total array values
.Close savechanges:=False
'close without saving
End With
Next vaFileName
ThisWorkbook.Worksheets("TOTAL").Range("AC47:AI299").Value = vaDataTotal
Application.ScreenUpdating = True
Else
MsgBox "There were no Excel files found."
End If
End With

End Sub
---------------------------------------------------

the problem that always pops up is
Run time error 1004
Unable to read the file

and the problematic line of code is
"Set wbkData = Workbooks.Open(Filename:=vaFileName)"


This code is the closest to what I've been trying to find on the net, and would be very glad if you could you give me help with this.

Thanks in advance!

 

[bojanpaun]

I found the error, it wasn't in the code, but in the file that suposed to be read. Anyway, the code works perfectly, and all the compliments to the creator!

Cheers!

 

[Udars]

I get a reply- "Object doesn't support this action". I use Excel 2010.
This question might sound funny, but m totally new to VBA..

Thanks for Help