Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
I have one folder that contains several workbooks. They are contracts and
have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
Try this code. Change client folder to the folder containing the workbooks
you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
Thanks Joel,
I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
Does the error occur on this line:
Worksheets("Master").Activate if yes, then that workbook doesn't have a worksheet named "Master". smonsmo wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
You'll get error 9 if the worksheet is not called Master. Maybe you have
extra spaces in the worksheet name? You can change the line below to match your real worksheetname. Worksheets("Master").Activate "smonsmo" wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
OK, I worked thru that problem, now I cant get it to actually grab any info.
It opens each workbook and then asks me if I want to save any changes. I would like it to open the workbooks, get the needed information and then close them automatically. But thats a problem for after I get it to take the information from MASTER! U3 and W29. "Dave Peterson" wrote: Does the error occur on this line: Worksheets("Master").Activate if yes, then that workbook doesn't have a worksheet named "Master". smonsmo wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
You can close without the prompt by using:
Workbooks(Filename).Close savechanges:=false (to discard any changes) Change W3 to W29 if you want that instead. smonsmo wrote: OK, I worked thru that problem, now I cant get it to actually grab any info. It opens each workbook and then asks me if I want to save any changes. I would like it to open the workbooks, get the needed information and then close them automatically. But thats a problem for after I get it to take the information from MASTER! U3 and W29. "Dave Peterson" wrote: Does the error occur on this line: Worksheets("Master").Activate if yes, then that workbook doesn't have a worksheet named "Master". smonsmo wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
Dave,
I made the change to the proper cell but still don't have the info show up. Thanks for the way to fix the file closing. It gets a bit annoying when you have 15 or 20 files in that folder. "Dave Peterson" wrote: You can close without the prompt by using: Workbooks(Filename).Close savechanges:=false (to discard any changes) Change W3 to W29 if you want that instead. smonsmo wrote: OK, I worked thru that problem, now I cant get it to actually grab any info. It opens each workbook and then asks me if I want to save any changes. I would like it to open the workbooks, get the needed information and then close them automatically. But thats a problem for after I get it to take the information from MASTER! U3 and W29. "Dave Peterson" wrote: Does the error occur on this line: Worksheets("Master").Activate if yes, then that workbook doesn't have a worksheet named "Master". smonsmo wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract from multiple workbooks in a folder
If the code is picking up U3, then it should be picking up the other cell, too.
But it's copy|pasting. Maybe that's not what you want. You may want to replace: Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) with Thisworkbook.activesheet.range("A" & rowcount).value = Range("U3").value Thisworkbook.activesheet.range("b" & rowcount).value = Range("W29").value smonsmo wrote: Dave, I made the change to the proper cell but still don't have the info show up. Thanks for the way to fix the file closing. It gets a bit annoying when you have 15 or 20 files in that folder. "Dave Peterson" wrote: You can close without the prompt by using: Workbooks(Filename).Close savechanges:=false (to discard any changes) Change W3 to W29 if you want that instead. smonsmo wrote: OK, I worked thru that problem, now I cant get it to actually grab any info. It opens each workbook and then asks me if I want to save any changes. I would like it to open the workbooks, get the needed information and then close them automatically. But thats a problem for after I get it to take the information from MASTER! U3 and W29. "Dave Peterson" wrote: Does the error occur on this line: Worksheets("Master").Activate if yes, then that workbook doesn't have a worksheet named "Master". smonsmo wrote: Thanks Joel, I copied this code and made the needed changes to the directory but when it runs I end up with this error. Runtime error 9 subscript out of range. I does open each file but does not copy any info. Any ideas? "Joel" wrote: Try this code. Change client folder to the folder containing the workbooks you want to get a summary from. The workbook that yo place this macro should not be in the same directory. Sub gettotals() Const Clientfolder = "c:\temp\test" RowCount = 1 first = True Do If first = True Then Filename = Dir(Clientfolder & "\*.xls") first = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open (Clientfolder & "\" & Filename) Worksheets("Master").Activate Range("U3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("A" & RowCount) Range("W3").Copy Destination:= _ ThisWorkbook.ActiveSheet.Range("B" & RowCount) Workbooks(Filename).Close RowCount = RowCount + 1 End If Loop While Filename < "" End Sub "smonsmo" wrote: I have one folder that contains several workbooks. They are contracts and have been saved to this folder based on their scheduled date for the work to be completed. (master!u3) Their names will be random. (Customer names last name, first name) I would like to extract the value found at Masterw30 which is the dollar total for the job from each workbook found in this folder. Then compile a list for each week of the month based on each workbooks scheduled date. This way I will be able to see the amount of work we have scheduled each week. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract cell data from multiple files in one folder | Excel Discussion (Misc queries) | |||
Password protect a folder with several workbooks | Excel Discussion (Misc queries) | |||
Copy a cell to all workbooks within a folder. | Excel Discussion (Misc queries) | |||
How do I extract cells from multiple workbooks | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |