PDA

View Full Version : Text to columns Destination



clvestin
02-09-2007, 01:44 PM
I'm opening a fixed width dbf file. I copy the file to column a of an excel sheet. I would like to parse the file with the result on Sheet2.
Public Sub t_to_col()
Set col1 = Sheets("sheet1").Columns(1)
Set col2 = Sheets("sheet2").Columns(1)
col1.Copy
Worksheets("sheet2").Activate
col2.Select
Sheets("sheet2").Paste
col2.TextToColumns
End Sub


Public Sub t_to_cols2()
Set col1 = Worksheets(1).Columns(1)
Set col2 = Worksheets(2).Columns(1)
Set rng1 = Worksheets(2).Range("a1:b2")
'Worksheets(2).Activate
'rng1.Activate 'Select
col1.TextToColumns DataType:=xlFixedWidth, Destination:=rng1

End Sub

The first routine will do it but is clumsy.(I don't like the whole Copy/Paste thing). The second is a no go, the result overwrites the data on Sheet 1.
Any suggestions?

mvidas
02-09-2007, 02:12 PM
Hi,

The text to columns method can't be used from one sheet to another, the destination has to be on the source data sheet. There is a faster way to move the data without selecting/etc:Public Sub t_to_col3()
Sheets("sheet2").Columns(1).Value = Sheets("sheet1").Columns(1).Value
Sheets("sheet2").Columns(1).TextToColumns 'etc
End SubMatt

clvestin
02-10-2007, 05:41 AM
Thanks, Matt. The documentation left that vital point uncovered.