Hi everyone!
I have also posted this on: Microsoft.Public.Excel

I have a number of excel files in a folder that need "Text to Columns" in
Column A. The files are all formatteed identically, with different data. Is
there a solution for this?

I'm using Excel 2003 on Windows XP.

TIA,
James

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

Dave,
Thank you so much for all your effort.

I did as you said, but i'm pretty sure i missed something.
I get a "compile error: expected end sub" and a "compile error: For control
variable already in use"

here's the code i recorded:

tex2column Macro
' Macro recorded 12/12/2006 by qc02530
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1)), _
TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-2
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Us#"
Range("B1").Select
ActiveCell.FormulaR1C1 = "date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "ubd"
Range("D1").Select
ActiveCell.FormulaR1C1 = "model"
Range("E1").Select
ActiveCell.FormulaR1C1 = "serial"
Range("F1").Select
ActiveCell.FormulaR1C1 = "quantity"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "4 digit model"
Columns("F:F").Select
Columns("E:E").EntireColumn.AutoFit
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Selection.AutoFill Destination:=Range("E2:E43")
Range("E2:E43").Select
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("H1").Select
ActiveCell.FormulaR1C1 = "combo"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2])"
Selection.AutoFill Destination:=Range("H2:H43")
Range("H2:H43").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-16
End Sub

"Dave Miller" wrote in message
oups.com...
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