Collect data from Excel files

[tim]

Hi
I have about 60 (incresing everyday) .xls files in a folder called
"customers invoices"

Format of all the files is the same.
How can I exract data from all these files (from this folder)?

I need to get data from
A6
B14
F34
F36

Any suggestion please.

 

[Joel]

Change folder in code below to the full directory name you require. The code
will put all the data in the active worksheet in column A - D. I also put
the filename in column E.

Sub getdata()

Folder = "c:\temp\"

Set oldsht = ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set newbk = Workbooks.Open(Filename:=Folder & FName)
With oldsht
.Range("A" & RowCount) = newbk.ActiveSheet.Range("A6")
.Range("B" & RowCount) = newbk.ActiveSheet.Range("B14")
.Range("C" & RowCount) = newbk.ActiveSheet.Range("F34")
.Range("D" & RowCount) = newbk.ActiveSheet.Range("F36")
.Range("E" & RowCount) = FName
End With
newbk.Close savechanges:=False
FName = Dir()
Loop
End Sub

 

[Joel]

I forgot to increment the RowCount

Sub getdata()

Folder = "c:\temp\"

Set oldsht = ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set newbk = Workbooks.Open(Filename:=Folder & FName)
With oldsht
.Range("A" & RowCount) = newbk.ActiveSheet.Range("A6")
.Range("B" & RowCount) = newbk.ActiveSheet.Range("B14")
.Range("C" & RowCount) = newbk.ActiveSheet.Range("F34")
.Range("D" & RowCount) = newbk.ActiveSheet.Range("F36")
.Range("E" & RowCount) = FName
End With
RowCount = RowCount + 1
newbk.Close savechanges:=False
FName = Dir()
Loop
End Sub

 

[stefan onken]

hi Tim,
you could use an add-in
http://xcell05.free.fr/morefunc/english/index.htm
the function is indirect.ext
http://xcell05.free.fr/morefunc/english/indirect.ext.htm

or you need a macro, that in a loop every workbook of a directory
-opens, copy/paste data, closes
if you want a macro, where/how should the data be pasted?

stefan

 

[tim]

thanks guys.
Joel I am sorry to forgot to mention.

The sheet name in the file I want data from is "invoice".

Data collected to be pasted in the sheet I am working called "OverView".

What changes I ned to make and where to pu the code?

regards

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/merge.htm

Or
http://www.rondebruin.nl/summary2.htm

 

[tim]

Thanks it was really helpful link.

I would still go for Joel suggested code.

Joel I have tried the following code but no luck. any further help would be
much appriciated.

Sub getdata()

Folder = "c:\customers_invoices"

Set oldsht = ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set newbk = Workbooks.Open(Filename:=Folder & FName)
With oldsht
.Range("A" & RowCount) = newbk.ActiveSheet.Range("A6")
.Range("B" & RowCount) = newbk.ActiveSheet.Range("B14")
.Range("C" & RowCount) = newbk.ActiveSheet.Range("F34")
.Range("D" & RowCount) = newbk.ActiveSheet.Range("F36")
.Range("E" & RowCount) = FName
End With
RowCount = RowCount + 1
newbk.Close savechanges:=False
FName = Dir()
Loop
End Sub