Batch Job "Text to column" in excel
James,
-Open one of the workbooks that you would like to format
-Record the macro you want to run on all of your workbooks
-Open the VBE(alt + F11)
-Copy the code you just recorded
-Open a new workbook, paste the code below into a new Module
-Replace 'Put your recorded macro here, with your recorded Macro
-Press F5 and watch the magic.
================================================== ==
Sub FormatABunchOfFiles()
Dim myBook As Workbook
Dim sDir, sFile, sCurrent As String
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With
sCurrent = ActiveWorkbook.Name
sDir = "YourDirectoryHere\"
sFile = "*.xls"
With Application.FileSearch
.NewSearch
.LookIn = sDir
.Filename = sFile
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute < 0 Then Exit Sub
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Sheets("Sheet1").Activate
'Put your recorded macro here
myBook.Save
myBook.Close
Next i
End With
Windows(sCurrent).Activate
End Sub
|