Taking data from multiple workbooks?

[Guest]

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!

 

[Guest]

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

 

[Guest]

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("B2224").Copy _

Destination:=Workbooks(ThisWorkbook.Name).Worksheets("Sheet1"). _
Range("A10").Offset(rowOffset:=0, columnOffset:=0)

Workbooks(MyfileName).Close SaveChanges:=False

Next i


End Sub

 

[Guest]

Thanks Joel! It worked

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("B2224").Copy _

Destination:=Workbooks(ThisWorkbook.Name).Worksheets("Sheet1"). _
Range("A10").Offset(rowOffset:=0, columnOffset:=0)

Workbooks(MyfileName).Close SaveChanges:=False

Next i


End Sub

 

[Ron de Bruin]

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