Now I am sure this problem has come up many times before, but I think I am nearly there. I have a few hundred excel files which i need to run some formatting on and then save as a text format with a specific header.

I have the batch processing bit sorted but it only saves as the original file not as text format. Here is what I have so far, hope you can help.

Thanks to Bob Philips who has helped with this unknowingly on another forum post.

Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
With .Worksheets(1)
Columns("A:A").Select
Selection.Copy
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "000"
Range("C1").Select
ActiveCell.FormulaR1C1 = "3"
Columns("A:C").Select
Selection.Copy
End With
.Save
.Close
End With
cnt = cnt + 1
End If
Next file

End If ' sFolder <> ""

End Sub