Multiple Workbook sums

[Wally Steadman]

Greetings all,
Sorry for posting this again. I am not sure I am explaining my situation
clearly and have looked through my Excel 2002 Powerprogramming with VBA and
I am still lost.

Let me explain what I am trying to accomplish and then give some detailed
examples.

Employee's must fill out a sheet weekly on their activities. once they fill
out their sheets, totals are calculated on another sheet (TOTALS) in their
workbook in a range of cells from
B227.

We are trying to compile their information into one master worksheet. All
workbooks are exactly the same format.

I am trying to create a sub that will start in cell B2 of the master and sum
cell B2 from all the other workbooks. Then it will move to cell B3 and sum
all Cell B3's from workbooks in the folder then it will go to B4, then B5,
through P27 summing up cells in the USER##.xls workbooks in the folder

All workbooks are named USER##.xls, where ## is the employee's user number.

Is there a way to have it look at all files in the folder that begin with
USER?

Each week there can be a different number of user files in the folder. Some
employees may be on vacation, and more employees may be hired so I am trying
for something that is flexible enough to catch those situations.

I am thinking I need to have variables that list the R1C1 notation and then
when it is in a cell, it will open all USER##.xls workbooks, one by one,
adding their value to a variable and when it gets to the last USER File, it
will go to the next cell and do the same thing all over again until it
finishes with cell P27.

Example of 1st Weeks files in the folder
master.xls
User1.xls
User2.xls
User3.xls
User7.xls
User8.xls

Example of 2nd week files in the folder
master.xls
User1.xls
User3.xls
User5.xls
User6.xls
User7.xls
User9.xls
User10.xls

If someone could help I would certainly appreciate it. It has been a while
since I did any VBA and while I have an idea of what I need to do, My mind
is not letting me even start.

Thanks in Advance

Wally Steadman

 

[Ingolf]

Hello Wally,

this little macro should work for you. I assumed it's sheet 1 to be
summed up.


Sub total_values()

Dim fso As Object
Dim fo As Object
Dim f As Object
Dim rng As Range

Set fso = CreateObject("Scripting.FileSystemObject")
Set fo = fso.GetFolder(ThisWorkbook.Path)

ThisWorkbook.Sheets(1).Range("B227").ClearContents

For Each f In fo.Files
If LCase(Left(f.Name, 4)) = "user" Then
If LCase(Right(f.Name, 4)) = ".xls" Then
Workbooks.Open Filename:=f.Path
For Each rng In ThisWorkbook.Sheets(1).Range("B227")
rng = rng + Workbooks(f.Name).Sheets(1).Range(rng.Address)
Next 'rng
Workbooks(f.Name).Close False
End If
End If
Next 'f

End Sub

Regards,
Ingolf

 

[strive4peace]

Hi Wally,

here is some code you can modify:

'~~~~~~~~~~~
Sub AddCellFromEveryWorkbook()

'strive4peace2007 at yahoo.com

On Error GoTo Proc_Err

Dim mPath As String, mMask As String, mFile As String
Dim mCellAddr As String, mTotal As Double
Dim WB As Workbook, WBactive As Workbook
Dim mBooUpdateLinks As Boolean

mPath = "C:\path\"
mMask = "USER*.xls"
mBooUpdateLinks = False

mCellAddr = "B3"
mTotal = 0
Set WBactive = ActiveWorkbook

mFile = Dir(mPath & mMask)

Do While mFile <> ""
Set WB = Workbooks.Open(mPath & mFile, _
mBooUpdateLinks)
mTotal = mTotal _
+ WB.ActiveSheet.Range(mCellAddr)
WB.Close False
mFile = Dir()
Loop

WBactive.ActiveSheet.Range(mCellAddr).Value = mTotal

Proc_Exit:
On Error Resume Next

WB.Close False
Set WB = Nothing

MsgBox "Done"
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " AddCellFromEveryWorkbook"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~~`

substituting the array method of addressing cells so you can
more easily set up a loop with the cell addresses ...

'~~~~~~~~~~~~~~~~~~~~
Sub AddCellFromEveryWorkbook()

'strive4peace2007 at yahoo.com

On Error GoTo Proc_Err

Dim mPath As String, mMask As String, mFile As String
Dim mRow As Long, mcol As Long, mTotal As Double
Dim WB As Workbook, WBactive As Workbook
Dim mBooUpdateLinks As Boolean

mPath = "C:\path\"
mMask = "USER*.xls"
mBooUpdateLinks = False

'B3 is row 3 column 2
mRow = 3
mcol = 2

mTotal = 0
Set WBactive = ActiveWorkbook

mFile = Dir(mPath & mMask)

Do While mFile <> ""
Set WB = Workbooks.Open(mPath & mFile, _
mBooUpdateLinks)
mTotal = mTotal _
+ WB.ActiveSheet.Cells(mRow, mcol)
WB.Close False
mFile = Dir()
Loop

WBactive.ActiveSheet.Cells(mRow, mcol).Value = mTotal

Proc_Exit:
On Error Resume Next

WB.Close False
Set WB = Nothing

MsgBox "Done"
Exit Sub

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & "
CalcOrderQtys"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~


there is no error checking here to make sure that the cell
you are adding up is numbers...


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Wally Steadman]

THANKS SO MUCH.
Your solution works like a champ. Not only will it allow them to sum up
weekly data, but with this very same form they can sum up any data.
Couple of learning questions for you:

1. Is ScriptingFileSystemObject an Excel Command type or just a name?
2. Where do you find the different things like ScriptingFileSystemObject if
it is a command?

Thanks again

Wally Steadman

 

[Wally Steadman]

I ended up using Ingolf's solution because it was the first one I got and it
worked effortlessly. I am going to try your solution as well, if for
nothing else than to learn more. I appreciate you taking the time to post
such a detailed reply. Thanks again for the help. Will let you know how
your solution helped and what learning I took from it

Wally Steadman

 

[Ingolf]

Hello Wally,

thanks for your reply. Nice to hear everything works fine. The
FileSystemObject isn't part of VBA. It's provided by an object library
(DLL-file) called Scripting Runtime, thus referenced by
"Scripting.FileSystemObject". Scripting Runtime is part of Windows and
usually included in the Windows installation (so with you, otherwise
the macro wouldn't have run). The FileSystemObject provides various
useful objects, methods and properties and is really worth taking a
look at. I've found two links in english for you, the first of which
giving you an introduction to using the FileSystemObject in VBA and the
latter looking to me like a good reference to everything the
FileSystemObject has to offer.

http://www.techbookreport.com/tutorials/fso1.html
http://www.tutorial-web.com/asp/fso/

Have fun
Ingolf