Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Taking data from multiple workbooks?
Hi,
I want to know how I can take data from multiple excel workbooks and have it be put into one giant list in another excel workbook. The header is the same for all of the data. It is different data for each state, so I have over 50. Instead of copying and pasting each one into another workbook, I'd like to automate it. Is there a way to write a macro that would take all of the excel files in a folder and take from the exact same sheet in each excel file the data and paste it onto one worksheet in another excel workbook? Thanks -Need Help Fast! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Taking data from multiple workbooks?
Does the number of data rows change within one state file? For example, if
the numbers are the populations of each county in one state, certainly the states differ in number of counties, but that number doesn't vary within one state. If not, let's say state A always has two counties: A1: "CountyPopulation" A2: 30,000 A3: 40,000 And state B always has five counties: A1: "CountyPopulation" A2: 50,000 A3: 60,000 A4: 70,000 A5: 80,000 A6: 90,000 Then the summary sheet should have: A1: "AllCountyPopulations" (or whatever) A2: =[StateA.xls]Sheet1:A2 A3: =[StateA.xls]Sheet1:A3 A4: =[StateB.xls]Sheet1:A2 A5: =[StateB.xls]Sheet1:A3 A6: =[StateB.xls]Sheet1:A4 A7: =[StateB.xls]Sheet1:A5 A8: =[StateB.xls]Sheet1:A6 This is rather easy to do: just as you were doing, open the summary workbook and one state workbook, select all the state data (not the header) in the state workbook, switch to the summary workbook, select the first blank cell, but then don't just paste, do Edit/Paste Special/Paste Link. After that, all you'd have to do is open the summary sheet and do Edit/Links/Update -- --Andy Smith "Need Help Fast!" wrote: Hi, I want to know how I can take data from multiple excel workbooks and have it be put into one giant list in another excel workbook. The header is the same for all of the data. It is different data for each state, so I have over 50. Instead of copying and pasting each one into another workbook, I'd like to automate it. Is there a way to write a macro that would take all of the excel files in a folder and take from the exact same sheet in each excel file the data and paste it onto one worksheet in another excel workbook? Thanks -Need Help Fast! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Taking data from multiple workbooks?
This should help. You need to fix source and destinattion files, but it
works really good Sub GetData() Set fs = Workbooks.Application.FileSearch With fs .LookIn = "c:\Temp" .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets("Sheet1").Range(" B22:D24").Copy _ Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _ Range("A10").Offset(rowOffset:=0, columnOffset:=0) Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub "Need Help Fast!" wrote: Hi, I want to know how I can take data from multiple excel workbooks and have it be put into one giant list in another excel workbook. The header is the same for all of the data. It is different data for each state, so I have over 50. Instead of copying and pasting each one into another workbook, I'd like to automate it. Is there a way to write a macro that would take all of the excel files in a folder and take from the exact same sheet in each excel file the data and paste it onto one worksheet in another excel workbook? Thanks -Need Help Fast! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Taking data from multiple workbooks?
Thanks Joel! It worked
"Joel" wrote: This should help. You need to fix source and destinattion files, but it works really good Sub GetData() Set fs = Workbooks.Application.FileSearch With fs .LookIn = "c:\Temp" .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets("Sheet1").Range(" B22:D24").Copy _ Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _ Range("A10").Offset(rowOffset:=0, columnOffset:=0) Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub "Need Help Fast!" wrote: Hi, I want to know how I can take data from multiple excel workbooks and have it be put into one giant list in another excel workbook. The header is the same for all of the data. It is different data for each state, so I have over 50. Instead of copying and pasting each one into another workbook, I'd like to automate it. Is there a way to write a macro that would take all of the excel files in a folder and take from the exact same sheet in each excel file the data and paste it onto one worksheet in another excel workbook? Thanks -Need Help Fast! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Taking data from multiple workbooks?
Hi Need Help Fast
I advise not to use FileSearch 1: have bugs 2: not working in Excel 2007 See this page for other solutions http://www.rondebruin.nl/copy3.htm But if it is working for your problem that it is OK -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Need Help Fast!" wrote in message ... Thanks Joel! It worked "Joel" wrote: This should help. You need to fix source and destinattion files, but it works really good Sub GetData() Set fs = Workbooks.Application.FileSearch With fs .LookIn = "c:\Temp" .FileType = msoFileTypeExcelWorkbooks .Execute End With For i = 1 To fs.FoundFiles.Count MyfileName = fs.FoundFiles(i) Workbooks.Open Filename:=MyfileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyfileName, "\") If CharPosition 0 Then MyfileName = Mid(MyfileName, CharPosition + 1) Else Exit Do End If Loop Workbooks(MyfileName).Worksheets("Sheet1").Range(" B22:D24").Copy _ Destination:=Workbooks(ThisWorkbook.Name).Workshee ts("Sheet1"). _ Range("A10").Offset(rowOffset:=0, columnOffset:=0) Workbooks(MyfileName).Close SaveChanges:=False Next i End Sub "Need Help Fast!" wrote: Hi, I want to know how I can take data from multiple excel workbooks and have it be put into one giant list in another excel workbook. The header is the same for all of the data. It is different data for each state, so I have over 50. Instead of copying and pasting each one into another workbook, I'd like to automate it. Is there a way to write a macro that would take all of the excel files in a folder and take from the exact same sheet in each excel file the data and paste it onto one worksheet in another excel workbook? Thanks -Need Help Fast! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract data from multiple workbooks | Excel Discussion (Misc queries) | |||
taking data from multiple cells and displaying in one cell | Excel Discussion (Misc queries) | |||
Data in multiple workbooks | Excel Worksheet Functions | |||
Totaling data from multiple workbooks. | Excel Discussion (Misc queries) | |||
how do i summarize data in multiple workbooks? | Excel Programming |