I have many excel files, would like to aggregate into one

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?
 
Loop,
open each workbook,
copy the rows of data to bottom of destination workbook,
close current workbook,
next file
save current workbook
 
Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).End(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub
 
Thanks Tom,

Looks impressing, but I don't understand all the details. I suppose it
should be loaded as a VBA-module and executed as a macro and the only changes
I have to do is to edit line 5 (the sPath-line) pointing out the
subdirectory.

But nothing at all happens when executed. Is there any requirement that the
Excel-files shall be shared or similar?

Thank you very much for your effort.

Regards,
Gunnar

Tom Ogilvy said:
Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).End(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy]

Gunnar said:
I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?
Click to expand...
Click to expand...
 
Hi again,

I now noticed the remark "Can't execute code in Break Mode".

/Gunnar

Tom Ogilvy said:
Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).End(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy]

Gunnar said:
I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?
Click to expand...
Click to expand...
 
Thanks Tom

I got it working now. I had a very typical error - a missing \ at the end
of sPath.

I am most grateful - a lot of work is not necessary any longer.

Regards,
Gunnar

Tom Ogilvy said:
Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).End(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy]

Gunnar said:
I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?
Click to expand...
Click to expand...
 
Your efforts are very nice. Please try to define the variabels names more
clearly and try to put more comments for easy understanding of beginners.
But code is very useful.

Tom Ogilvy said:
Assume all the files (and only the files) are in a subdirectory - C:\MyFolder
for illustration

Sub GatherInformation()
Dim sPath as String, sName as String
Dim bk as Workbook, rng as Range
Dim rng1 as Range
sPath = "C:\MyFolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sName)
set rng = Thisworkbooks.worksheets(1).Cells(rows.count,1).End(xlup)(2)
with bk.Worksheets(1)
set rng1 = .Range(.Cells(1,1),.cells(rows.count,1).End(xlup))
end with
rng1.entirerow.copy destination:=rng
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy]

Gunnar said:
I have by bad system design in earlier stage received about 500 pcs of small
individual Excel files of exakt the same design but with data of different
items. Each file normally holds between 20 - 30 records (rows).
I now have a need to aggregate all these files into one single Excel or
Access file and would like to avoid all the work with to open each invidual
file and copy and paste.
Does any kind person know any practical method available?
Click to expand...
Click to expand...
 
Back
Top