Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Hello All,
I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Rashid,
See the sub below. This version puts labels into the first two columns to show the book and sheet from whence they came: if you don't like the labels, simply delete the first two columns after you're done. HTH, Bernie MS Excel MVP Sub Consolidate() ' Will consolidate Mulitple Sheets ' from Multiple Files onto one sheet ' Never tested with files that would ' give more than one sheets as end result ' Assumes that all data starts in cell A1 and ' is contiguous, with no blanks in column A With Application ..DisplayAlerts = False ..EnableEvents = False ..ScreenUpdating = False End With With Application.FileSearch ..NewSearch 'Change this to your directory ..LookIn = "C:\Temp" ..FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set Basebook = ThisWorkbook For i = 1 To .FoundFiles.Count If .FoundFiles(i) < ThisWorkbook.FullName Then Set myBook = Workbooks.Open(.FoundFiles(i)) For Each mySheet In myBook.Worksheets mySheet.Activate Range("A1").CurrentRegion.Copy _ Basebook.Worksheets(1).Range("C65536").End(xlUp).O ffset(1, 0) With Basebook.Worksheets(1) ..Range(.Range("A65536").End(xlUp).Offset(1, 0), _ ..Range("C65536").End(xlUp).Offset(0, -2)).Value = _ myBook.Name ..Range(.Range("B65536").End(xlUp).Offset(1, 0), _ ..Range("C65536").End(xlUp).Offset(0, -1)).Value = _ mySheet.Name End With Next mySheet myBook.Close End If Next i End If End With With Application ..DisplayAlerts = True ..EnableEvents = True ..ScreenUpdating = True End With Basebook.SaveAs Application.GetSaveAsFilename End Sub "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Start here Rashid
http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Hi Bernie,
After running your code. I got the following results: MyBook.xls Sheet1 MyBook.xls Sheet1 MyBook.xls Sheet2 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet1 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet2 MyBook.xls Sheet1 What I mean.. I did not get any values but instead I am getting the FileName and the SheetName What can be the problem? Rashid "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Rashid, See the sub below. This version puts labels into the first two columns to show the book and sheet from whence they came: if you don't like the labels, simply delete the first two columns after you're done. HTH, Bernie MS Excel MVP Sub Consolidate() ' Will consolidate Mulitple Sheets ' from Multiple Files onto one sheet ' Never tested with files that would ' give more than one sheets as end result ' Assumes that all data starts in cell A1 and ' is contiguous, with no blanks in column A With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Temp" .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set Basebook = ThisWorkbook For i = 1 To .FoundFiles.Count If .FoundFiles(i) < ThisWorkbook.FullName Then Set myBook = Workbooks.Open(.FoundFiles(i)) For Each mySheet In myBook.Worksheets mySheet.Activate Range("A1").CurrentRegion.Copy _ Basebook.Worksheets(1).Range("C65536").End(xlUp).O ffset(1, 0) With Basebook.Worksheets(1) .Range(.Range("A65536").End(xlUp).Offset(1, 0), _ .Range("C65536").End(xlUp).Offset(0, -2)).Value = _ myBook.Name .Range(.Range("B65536").End(xlUp).Offset(1, 0), _ .Range("C65536").End(xlUp).Offset(0, -1)).Value = _ mySheet.Name End With Next mySheet myBook.Close End If Next i End If End With With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With Basebook.SaveAs Application.GetSaveAsFilename End Sub "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Hi Ron,
Thanks for the reply.. I found many things interesting over there. I would give it a try and post back if there is any problems Rashid "Ron de Bruin" wrote in message ... Start here Rashid http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Try to understand the examples
For you situation you must loop through all your sheets in each workbook and copy the usedrange from each sheet. Remember you have only 65536 rows and the usedrange can be bigger then your data. http://www.contextures.com/xlfaqApp.html#Unused If you need help post back -- Regards Ron de Bruin http://www.rondebruin.nl "Rashid Khan" wrote in message ... Hi Ron, Thanks for the reply.. I found many things interesting over there. I would give it a try and post back if there is any problems Rashid "Ron de Bruin" wrote in message ... Start here Rashid http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation from many Workbooks to One Workbook
Rashid,
The code is written based on finding the data table starting in A1, and being contiguous. That apparently isn't the case, so the code need to be modified: change the A1 in the line Range("A1").CurrentRegion.Copy to any cell that will always be in your data table. HTH, Bernie MS Excel MVP "Rashid Khan" wrote in message ... Hi Bernie, After running your code. I got the following results: MyBook.xls Sheet1 MyBook.xls Sheet1 MyBook.xls Sheet2 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet1 MyBook.xls Sheet1 MyBook.xls Sheet3 MyBook.xls Sheet2 MyBook.xls Sheet1 What I mean.. I did not get any values but instead I am getting the FileName and the SheetName What can be the problem? Rashid "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Rashid, See the sub below. This version puts labels into the first two columns to show the book and sheet from whence they came: if you don't like the labels, simply delete the first two columns after you're done. HTH, Bernie MS Excel MVP Sub Consolidate() ' Will consolidate Mulitple Sheets ' from Multiple Files onto one sheet ' Never tested with files that would ' give more than one sheets as end result ' Assumes that all data starts in cell A1 and ' is contiguous, with no blanks in column A With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With With Application.FileSearch .NewSearch 'Change this to your directory .LookIn = "C:\Temp" .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Set Basebook = ThisWorkbook For i = 1 To .FoundFiles.Count If .FoundFiles(i) < ThisWorkbook.FullName Then Set myBook = Workbooks.Open(.FoundFiles(i)) For Each mySheet In myBook.Worksheets mySheet.Activate Range("A1").CurrentRegion.Copy _ Basebook.Worksheets(1).Range("C65536").End(xlUp).O ffset(1, 0) With Basebook.Worksheets(1) .Range(.Range("A65536").End(xlUp).Offset(1, 0), _ .Range("C65536").End(xlUp).Offset(0, -2)).Value = _ myBook.Name .Range(.Range("B65536").End(xlUp).Offset(1, 0), _ .Range("C65536").End(xlUp).Offset(0, -1)).Value = _ mySheet.Name End With Next mySheet myBook.Close End If Next i End If End With With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With Basebook.SaveAs Application.GetSaveAsFilename End Sub "Rashid Khan" wrote in message ... Hello All, I am using Office XP and I wish to extract data from many workbooks in the directory C:\Temp to a new Workbook and save it under a new name: The data are in rows for eg (Name can be anything... *.xls) Workbook1.xls, (Sheets 1, 2, .... ) (Workbook2.xls.....(Sheets 1, 2,3,4 .... ) and many more Workbooks all in C:\Temp The new Workbook should have all the data from the above Workbooks copied on Sheet1, Column A down. Can this be achieved? TIA Rashid Khan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Consolidation across workbooks | Excel Discussion (Misc queries) | |||
Collecting data from different workbooks into summary workbook | Excel Discussion (Misc queries) | |||
How to get sum of data in series from 2 Workbooks to another Workbook | Links and Linking in Excel | |||
Consolidation of data from cell in active sheet of closed workbook | Excel Worksheet Functions | |||
Need help data consolidation multiple workbooks | Excel Programming |