To open an excel file.

[Shaijuzacharia]

Hi,
I have got a folder ith Excel files of Excel 2003 and 2007.
I want to open each of these file and copy some data and paste it to
another workbook.
For example:
Suppose I have a folder named Data on my desktop with Excel files
1.xls,2.xls etc and a new.xls the work book I have made.
I want to open 1.xls and copy the contents in A1 and paste in the A1
of new.xls
and then copy A1 of 2.xls and paste in the B1 of new.xls.

Thanks in advance.
Shaiju

 

[Bob Phillips]

Sub LoopFiles()
Dim this As Worksheet
Dim FSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim NextCol As Long

Set this = Workbooks("new.xls").Worksheets(1)

Set FSO = CreateObject("Scripting.FileSystemObject")

Set Folder = FSO.GetFolder("c:\MyTest")

NextCol = 1
For Each file In Folder.Files
If file.Type Like "Microsoft*Excel*Worksheet*" Then
Workbooks.Open Filename:=file.Path
Range("A1").Copy this.Cells(1, NextCol)
ActiveWorkbook.Close savechanges:=False
End If
Next file

Set FSO = Nothing
Set Folder = Nothing
Set file = Nothing
Set this = Nothing

End Sub