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

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

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

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

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

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

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

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

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