View Single Post
  #4 (permalink)  
Old December 19th, 2005, 12:34 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

Oops silly me, did I forget the Text to columns bit! Sorry


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

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), OtherChar:="|"
End Sub

Cheers

Matt

Reply With Quote