Summarizing data in multiple excel files

[Ed R]

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

 

[Ron de Bruin]

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

 

[Peter]

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)




"Ed R" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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.

 

[Peter]

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)




"Peter" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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

 

[Peter]

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)




"Peter" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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