If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
how to combine several files, all with same columns, into one
Thanks for the response. The blank rows will occur below the data in each of
the files to be combined. Mike "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#12
|
|||
|
|||
how to combine several files, all with same columns, into one
I'm using the macro in the Excel file downloaded from your site. Thanks.
"Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#13
|
|||
|
|||
how to combine several files, all with same columns, into one
I think you use the FSO download from
http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#14
|
|||
|
|||
how to combine several files, all with same columns, into one
Yes, I'm using the FSO download. I am using a fixed range because row 1 of
each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#15
|
|||
|
|||
how to combine several files, all with same columns, into one
Read good
If the header is in row 1 the enter A2 in StartCell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Yes, I'm using the FSO download. I am using a fixed range because row 1 of each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#16
|
|||
|
|||
how to combine several files, all with same columns, into one
Okay got it. However, it's still pulling "blank" rows from the files to be
combined. The rows aren't really blank, because there are drop-down menus in 2 columns and conditional formulae in 3 (the formulae make the cell "" when FALSE). When the drop-downs are set to blank, and the formulae are all false, the row is blank, but still gets pulled into the combined file. Using your RDB_Filter_Value macro can make the "blank" lines disappear, but that macro cannot be used when the files to be combined are Protected, and this is necessary. Any way around these problems? Thanks again, Mike "Ron de Bruin" wrote: Read good If the header is in row 1 the enter A2 in StartCell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Yes, I'm using the FSO download. I am using a fixed range because row 1 of each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#17
|
|||
|
|||
how to combine several files, all with same columns, into one
I've solved all the problems except two:
1. After using the RDB_Filter_Values, my Excel Properties "Calculation" option is set to "manual". 2. The Autofilters on the files which were combined become non-functional--they have to be removed and re-installed to make them work again. I see there is code to prevent that, but it doesn't appear to be working. Thanks, Mike "Heliocracy" wrote: Okay got it. However, it's still pulling "blank" rows from the files to be combined. The rows aren't really blank, because there are drop-down menus in 2 columns and conditional formulae in 3 (the formulae make the cell "" when FALSE). When the drop-downs are set to blank, and the formulae are all false, the row is blank, but still gets pulled into the combined file. Using your RDB_Filter_Value macro can make the "blank" lines disappear, but that macro cannot be used when the files to be combined are Protected, and this is necessary. Any way around these problems? Thanks again, Mike "Ron de Bruin" wrote: Read good If the header is in row 1 the enter A2 in StartCell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Yes, I'm using the FSO download. I am using a fixed range because row 1 of each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#18
|
|||
|
|||
how to combine several files, all with same columns, into one
You can add code to unprotect the worksheet and protect the worksheet in the Get_Filter macro if you want
Is the worksheet password the same in all files ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Okay got it. However, it's still pulling "blank" rows from the files to be combined. The rows aren't really blank, because there are drop-down menus in 2 columns and conditional formulae in 3 (the formulae make the cell "" when FALSE). When the drop-downs are set to blank, and the formulae are all false, the row is blank, but still gets pulled into the combined file. Using your RDB_Filter_Value macro can make the "blank" lines disappear, but that macro cannot be used when the files to be combined are Protected, and this is necessary. Any way around these problems? Thanks again, Mike "Ron de Bruin" wrote: Read good If the header is in row 1 the enter A2 in StartCell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Yes, I'm using the FSO download. I am using a fixed range because row 1 of each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#19
|
|||
|
|||
how to combine several files, all with same columns, into one
If the code crash you have this problem
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I've solved all the problems except two: 1. After using the RDB_Filter_Values, my Excel Properties "Calculation" option is set to "manual". 2. The Autofilters on the files which were combined become non-functional--they have to be removed and re-installed to make them work again. I see there is code to prevent that, but it doesn't appear to be working. Thanks, Mike "Heliocracy" wrote: Okay got it. However, it's still pulling "blank" rows from the files to be combined. The rows aren't really blank, because there are drop-down menus in 2 columns and conditional formulae in 3 (the formulae make the cell "" when FALSE). When the drop-downs are set to blank, and the formulae are all false, the row is blank, but still gets pulled into the combined file. Using your RDB_Filter_Value macro can make the "blank" lines disappear, but that macro cannot be used when the files to be combined are Protected, and this is necessary. Any way around these problems? Thanks again, Mike "Ron de Bruin" wrote: Read good If the header is in row 1 the enter A2 in StartCell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... Yes, I'm using the FSO download. I am using a fixed range because row 1 of each workbook to be combined is a header row, and I don't want my combined file to contain every header row from each of the files to be combined. I will live with that if I have to, though. Thanks for your help! Mike "Ron de Bruin" wrote: I think you use the FSO download from http://www.rondebruin.nl/fso.htm Do you use a fixed range ? If you enter StartCell then it will copy till the last cell with data from that cell Read the info on the webpage In the "Get_Data" macro in the Get_Data_Macro" module change 'Add a new workbook with one sheet named "Combine Sheet" Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) BaseWks.Name = "Combine Sheet" To 'Use the Activesheet to paste the data Set BaseWks = Activesheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using the macro in the Excel file downloaded from your site. Thanks. "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
#20
|
|||
|
|||
how to combine several files, all with same columns, into one
Hi Ron,
If the blank rows are within the data is it possible to skip those rows? "Ron de Bruin" wrote: Hi Heliocracy 1: Yes that is possible, which macro do you use ? If you use one from the Dir page then chnage 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) rnum = 1 To Set BaseWks = Worksheets("Yoursheet") rnum = 2 2: Are the blank rows in the data or below the data ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Heliocracy" wrote in message ... I'm using your macros as well, but each of the files I'm trying to combine will have many blank rows on them...is there a way to modify your macros so that: 1. The combined data can be placed on a worksheet of my choosing, that already exists, starting on Row 2? 2. Blank rows are ignored when combining, so they don't end up in the combined worksheet? Great code BTW, and thanks in advance for your help! Heliocracy "Ron de Bruin" wrote: Look at this page for another way (see also the add-in) http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Katie" wrote in message ... Hello- I have used this code (trying to do my homework first!) and changed the path- it looks as though when it runs it opens all of the files and closes them but does not copy any data into the book1 file that I have opened - what am I doing wrong here? "Bernie Deitrick" wrote: Landa, Assumptions a data starts in cell A1, the table is contiguous, is on the first sheet of the workbook, and all 100 files are in one folder. Also, the total in all files is less than 65536 rows of data. Copy the macro below into a codemodule of a new workbook, change the path where indicated, and run it. When it is done, save the workbook. HTH, Bernie MS Excel MVP Sub Consolidate() Dim myBook As Workbook Dim myCalc As XlCalculation Dim myShtName As String With Application .EnableEvents = False .DisplayAlerts = False myCalc = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Excel\Files to combine" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then For i = 1 To .FoundFiles.Count Set myBook = Workbooks.Open(.FoundFiles(i)) myBook.Worksheets(1).Range("A1").CurrentRegion.Cop y _ ThisWorkbook.Sheets(1).Range("A65536").End(xlUp)(2 ) myBook.Close False Next i Else: MsgBox "There were no files found." End If End With With Application .EnableEvents = True .DisplayAlerts = True .Calculation = myCalc End With End Sub "Landa" wrote in message ... Let say there is 3 excel files. Each file has one sheet and the same columns, e.g. column A: product name column B: description Column C: Price How can I combine all the 3 sheets in different files into one sheet of a new file? I don't want to copy and paste, because in reality, I have more than 100 files like this. Thank you! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combine worksheets from different excel files into one excel file | dbguy11 | General Discussion | 2 | February 27th, 2006 03:06 PM |
Combine two Access files of the together in one | atlskw | Running & Setting Up Queries | 1 | December 6th, 2005 01:45 PM |
combine several ms office files into a linked master document? | Anne | General Discussions | 3 | November 8th, 2005 02:51 PM |
Cannot access read-only documents. | tomgillane | General Discussion | 14 | February 7th, 2005 10:53 PM |
How do I combine 2 text columns in Microsoft Excel? | Domaniman | General Discussion | 2 | October 5th, 2004 10:32 PM |