View Single Post
  #3 (permalink)  
Old December 19th, 2005, 12:28 PM
mjppaba mjppaba is offline
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

Alternatively try this for size!!

Sub mdl_ForEachFileInFiles()

Dim myCurrDir As String
Dim myFScript
Dim myFolder
Dim myFile
Dim myFileCollection
Dim myString
Dim mySheetName As String
Dim myCurrWkBk As String
myCurrDir = "C:\TestData\"
myCurrWkBk = ActiveWorkbook.Name

Set myFScript = CreateObject("Scripting.FileSystemObject")
Set myFolder = myFScript.GetFolder(myCurrDir)
Set myFileCollection = myFolder.Files


For Each myFile In myFileCollection
    Workbooks.Open (myCurrDir & "\" & myFile.Name)
    mySheetName = ActiveSheet.Name
    Range("A1").CurrentRegion.Copy
    Workbooks(myCurrWkBk).Sheets(1).Activate
    Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Workbooks(myFile.Name).Close savechanges:=False

Next


End Sub


cheers

Matt


Reply With Quote