| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summarizing multiple worksheet data | Excel Worksheet Functions | |||
| Multiple Worksheets, multiple lines and summarizing into one works | New Users to Excel | |||
| Excel 2007 - Summarizing Data | Excel Discussion (Misc queries) | |||
| Reading data arrays from multiple data files in excel | Excel Discussion (Misc queries) | |||
| Summarizing the data from all the files in a folder | Excel Discussion (Misc queries) | |||
![Ed R[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
