Summarizing data in multiple excel files
 

All of our invoices are seperate excel files. How can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks

Summarizing data in multiple excel files
 

Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote in message ...
All of our invoices are seperate excel files. How can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks

Summarizing data in multiple excel files
 

Hi Ron, that works well for sequential retrievals, but do
you have any examples of code that will allow you to
search a column looking for matches to a value such as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote in
message ...
All of our invoices are seperate excel files. How can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks


.

Summarizing data in multiple excel files
 

Hi Peer

If you find what you are looking for in the column what do you want to do
Copy the row or ???

Tell exactly what you want to do and I will try to help you today or Tomorrow.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in message ...
Hi Ron, that works well for sequential retrievals, but do
you have any examples of code that will allow you to
search a column looking for matches to a value such as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote in
message ...
All of our invoices are seperate excel files. How can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks


.

Summarizing data in multiple excel files
 

If I find what I'm looking for I need to copy certain
cells of that row not the entire row.

Thanks
-----Original Message-----
Hi Peer

If you find what you are looking for in the column what
do you want to do
Copy the row or ???

Tell exactly what you want to do and I will try to help
you today or Tomorrow.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
Hi Ron, that works well for sequential retrievals, but
do
you have any examples of code that will allow you to
search a column looking for matches to a value such as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote in
message ...
All of our invoices are seperate excel files. How
can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks


.



.

Summarizing data in multiple excel files
 

Try this
I did a fast test and it seems to work corect

It will look for "ron" in the Acolumn in the first worksheet in every workbook in the folder
If it is found it will copy the cell in a,c,e,g of that row to the basebook

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim a As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 0
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

With mybook.Worksheets(1)
For r = .UsedRange.Rows.Count To 1 Step -1
If Trim(.Cells(r, "A").Value) = "ron" Then
rnum = rnum + 1
.Cells(r, 1).Range("A1,C1,E1,G1").Copy _
Destination:=basebook.Worksheets(1).Cells(rnum, 1)
End If
Next
End With

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in message ...
If I find what I'm looking for I need to copy certain
cells of that row not the entire row.

Thanks
-----Original Message-----
Hi Peer

If you find what you are looking for in the column what
do you want to do
Copy the row or ???

Tell exactly what you want to do and I will try to help
you today or Tomorrow.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
Hi Ron, that works well for sequential retrievals, but
do
you have any examples of code that will allow you to
search a column looking for matches to a value such as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote in
message ...
All of our invoices are seperate excel files. How
can I
easily summarize them (grab certain cells) from every
file in a specific folder?

Thanks


.



.

Summarizing data in multiple excel files
 

Hi Ron, thanks for the code this looks like it will do the
trick. My boss now wants me to check the input from a
userform for a certain piece of data. The input is being
stored in "y1" of my worksheet. Can I just substitute y1
for ron in the code??

Thanks for all of your help and patience with this newbie.

-----Original Message-----
Try this
I did a fast test and it seems to work corect

It will look for "ron" in the Acolumn in the first
worksheet in every workbook in the folder
If it is found it will copy the cell in a,c,e,g of that
row to the basebook

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim a As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 0
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

With mybook.Worksheets(1)
For r = .UsedRange.Rows.Count To 1 Step -1
If Trim(.Cells(r, "A").Value) = "ron" Then
rnum = rnum + 1
.Cells(r, 1).Range
("A1,C1,E1,G1").Copy _
Destination:=basebook.Worksheets
(1).Cells(rnum, 1)
End If
Next
End With

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
If I find what I'm looking for I need to copy certain
cells of that row not the entire row.

Thanks
-----Original Message-----
Hi Peer

If you find what you are looking for in the column what
do you want to do
Copy the row or ???

Tell exactly what you want to do and I will try to help
you today or Tomorrow.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
Hi Ron, that works well for sequential retrievals,
but
do
you have any examples of code that will allow you to
search a column looking for matches to a value such
as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote
in
message news:07c401c3af8e$d7c7d3c0
...
All of our invoices are seperate excel files. How
can I
easily summarize them (grab certain cells) from
every
file in a specific folder?

Thanks


.



.



.

Summarizing data in multiple excel files
 

Hi Peter

Try this

Place this two lines outside the loop

Dim findstring As String
findstring = ThisWorkbook.Sheets("Sheet1").Range("Y1").Value


And use this in the loop
If Trim(.Cells(r, "A").Value) = findstring Then


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in message ...
Hi Ron, thanks for the code this looks like it will do the
trick. My boss now wants me to check the input from a
userform for a certain piece of data. The input is being
stored in "y1" of my worksheet. Can I just substitute y1
for ron in the code??

Thanks for all of your help and patience with this newbie.

-----Original Message-----
Try this
I did a fast test and it seems to work corect

It will look for "ron" in the Acolumn in the first
worksheet in every workbook in the folder
If it is found it will copy the cell in a,c,e,g of that
row to the basebook

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim a As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 0
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

With mybook.Worksheets(1)
For r = .UsedRange.Rows.Count To 1 Step -1
If Trim(.Cells(r, "A").Value) = "ron" Then
rnum = rnum + 1
.Cells(r, 1).Range
("A1,C1,E1,G1").Copy _
Destination:=basebook.Worksheets
(1).Cells(rnum, 1)
End If
Next
End With

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
If I find what I'm looking for I need to copy certain
cells of that row not the entire row.

Thanks
-----Original Message-----
Hi Peer

If you find what you are looking for in the column what
do you want to do
Copy the row or ???

Tell exactly what you want to do and I will try to help
you today or Tomorrow.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Peter" wrote in
message ...
Hi Ron, that works well for sequential retrievals,
but
do
you have any examples of code that will allow you to
search a column looking for matches to a value such
as a
state abbreviation?

-----Original Message-----
Try
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed R" wrote
in
message news:07c401c3af8e$d7c7d3c0
...
All of our invoices are seperate excel files. How
can I
easily summarize them (grab certain cells) from
every
file in a specific folder?

Thanks


.



.



.