New Question: Text To Column (Macro)

[Superstar31]

Ok, with all my talk about Macros' I need to create a macro that will change text to column in a file.

The columns have set column widths of
0-12-29-43-53-57-64-72-85-92-100-108-121

Is this possible? or should I make a macro just using the record button? which I don't know how to use.

Thanks

 

[Norie]

I would recommend recording a macro.

All you need to do is goto Tools>Macros...Record New Macro... then manually carry out the Data>Text to Columns... as required.

 

[Superstar31]

Hmm ok, going to try doing that. Now will I be able to move this macro from workbook to workbook with ease?

 

[Norie]

Not sure what you mean.

If you want the macro to be available to all workbooks I think what you do is put it in your Personal.xls workbook.

I'm afraid that's not a subject I know much about but I'm sure a search on the forum would be useful.

If you just want to run the macro on specific worksheets/workbooks then you should just be able to change references in the code.

 

[Superstar31]

Ok, learning from my 1st post, let me be more specific from now.

I take data from a file that has no extension(named marc1234). The file name will always be the same, and when I'm done I have to save it as an excel workbook with a different name to refrence the dates I'm using. I then open it with excel 2003.

I get a error message "This file is not a recgonizable format" I then click ok, and get what seems to the naked eye garbage. I then click on the first row and click "text to column" and make my adjustments. After I complete that I do some more editing, that I'll get to later

 

[Superstar31]

Sweet, I need to store it in

"Personal Macro WorkBook" and I can use it every time I open a file.

so my macro looks like this

' My_1st_Macro Macro
' Macro recorded 11/28/2005 by XXXXXX
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(12, 1), Array(29, 1), Array(43, 1), Array(53, 1), _
Array(57, 1), Array(64, 1), Array(72, 1), Array(85, 1), Array(92, 1), Array(100, 1), Array( _
108, 1), Array(121, 1)), TrailingMinusNumbers:=True
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").ColumnWidth = 8.57
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub