Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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 B2:P27. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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("B2:P27").ClearConten ts 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("B2:P27") 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 Wally Steadman schrieb: 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 B2:P27. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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 wrote: 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 B2:P27. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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 "Ingolf" wrote in message oups.com... 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("B2:P27").ClearConten ts 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("B2:P27") 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 Wally Steadman schrieb: 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 B2:P27. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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 "strive4peace" <"strive4peace2006 at yahoo dot com" wrote in message ... 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 wrote: 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 B2:P27. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Workbook sums
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 Wally Steadman schrieb: 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 "Ingolf" wrote in message oups.com... 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("B2:P27").ClearConten ts 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("B2:P27") 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 Wally Steadman schrieb: 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 B2:P27. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sums with conditions and multiple columns | Excel Discussion (Misc queries) | |||
sumif with multiple sums | Excel Worksheet Functions | |||
adding multiple sums w/ different criteria | Excel Discussion (Misc queries) | |||
multiple entries-sums to different cells | Excel Worksheet Functions | |||
Excell will not add multiple sums together, what do I have turned. | Excel Discussion (Misc queries) |