Collect data from Excel files
 

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.

Collect data from Excel files
 

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

"tim" wrote:

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.

Collect data from Excel files
 

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

"tim" wrote:

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.

Collect data from Excel files
 

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/engl...direct.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



On 22 Mai, 14:32, "tim" wrote:
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.

Collect data from Excel files
 

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

"tim" wrote in message
...
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.

Collect data from Excel files
 

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

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"tim" wrote in message ...
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.

Collect data from Excel files
 

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



"Ron de Bruin" wrote in message
...
See also
http://www.rondebruin.nl/merge.htm

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"tim" wrote in message
...
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.