Collect data from Excel files

  • Thread starter Thread starter tim
  • Start date Start date
T

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.
 
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
 
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
 
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
 
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
 
Back
Top