Hi everyone!
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

Highlight the range you want to convert...

Then select your macro recorder to record your Text to Column convert...

Then when you need it again, select the affected range and use your play
macro to do it automatically.

Note: You could always use the CTL-SHIFT-DOWN ARROW to select the affected
range if the data is all contiguous.


"jjc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi everyone!
> 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
>

Chuck, you are absolutely right on with what i'm looking for.

A kind fellow named Dave Miller wrote a macro for me on another group, but i
couldn't get it to work.

If you need the directory, i could use: C:\repfiles
and as for the parameters for text to colums: Delimited using Commas.

here is the message from Mr. Miller:

"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


"CLR" <(E-Mail Removed)> wrote in message
news:BD99F1C2-11B7-4B66-9484-(E-Mail Removed)...
> What you want to do requires a custom macro. It would have to open each
> file
> in a directory, perform the TextToColumns function, and then save and
> close
> the file, (maybe to a new name)...then go on and do the next one untill
> all
> had been processed. To write such a macro, one would need to know the
> exact
> path and directory name, as well as the exact parameters you desired in
> the
> TextToColumns function. If you're up for some VBA, and want to provide
> the
> needed information.....post back and someone will help.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "jjc" wrote:
>
>> Hi everyone!
>> 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
>>
>>
>>

Hi James..........
I just now saw your response and now I gotta crash, but if no one has
responded by morning, I'll take a look at it then........

Vaya con Dios,
Chuck, CABGx3



"jjc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Chuck, you are absolutely right on with what i'm looking for.
>
> A kind fellow named Dave Miller wrote a macro for me on another group, but
i
> couldn't get it to work.
>
> If you need the directory, i could use: C:\repfiles
> and as for the parameters for text to colums: Delimited using Commas.
>
> here is the message from Mr. Miller:
>
> "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
>
>
> "CLR" <(E-Mail Removed)> wrote in message
> news:BD99F1C2-11B7-4B66-9484-(E-Mail Removed)...
> > What you want to do requires a custom macro. It would have to open each
> > file
> > in a directory, perform the TextToColumns function, and then save and
> > close
> > the file, (maybe to a new name)...then go on and do the next one untill
> > all
> > had been processed. To write such a macro, one would need to know the
> > exact
> > path and directory name, as well as the exact parameters you desired in
> > the
> > TextToColumns function. If you're up for some VBA, and want to provide
> > the
> > needed information.....post back and someone will help.
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "jjc" wrote:
> >
> >> Hi everyone!
> >> 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,

This is the recorded macro and my macro put together:
-Paste this into a module and let me know if it works.

David Miller

==============================================

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
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
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1") = "Us#"
Range("B1") = "date"
Range("C1") = "ubd"
Range("D1") = "model"
Range("E1") = "serial"
Range("F1") = "quantity"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1") = "4 digit model"
Columns("E:E").EntireColumn.AutoFit
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
Selection.AutoFill Destination:=Range("E2:E43")
Range("E2:E43").Select
Range("H1") = "combo"
Range("H2").Select
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-3],RC[-2])"
Selection.AutoFill Destination:=Range("H2:H43")
Columns("H:H").EntireColumn.AutoFit
myBook.Save
myBook.Close
Next i
End With
Windows(sCurrent).Activate
End Sub