Prev: Hyperlink events
Next: excel 2007 errors on chart changes
From: Nadeem on 9 Feb 2010 23:25

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" wrote:

> 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" wrote:
>
> > 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?
 | 
Pages: 1
Prev: Hyperlink events
Next: excel 2007 errors on chart changes