Comma delimited question

[Carol]

When I paste several lines of comma delimited text into a worksheet
I get the icon to use the text import wizard. Then I have to select
delimited and comma everytime I paste.
How can I set that as the default for that worksheet?
As I paste entries several times it becomes rather annoying.
Any help would be appreciated.

Carol

 

[Dave Peterson]

It's been my experience that excel likes to remember what you used the previous
time (within the same excel session).

So once you do data|text to columns, excel will remember this when you paste
that comma delimited stuff the second time.

If you close the workbook, then excel forgets and goes back to its default.

I don't think you can change the default, but you can take advantage of excel's
"helpfulness".

You could make a dummy workbook and put it in your xlStart folder. Have a macro
in that workbook that does a texttocolumns the way you want. Then closes and
gets out of the way.

Option Explicit
Private Sub Auto_Open()

With ThisWorkbook.Worksheets(1).Range("a1")
.Value = "asdf"
.TextToColumns Destination:=.Columns(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False
End With

ThisWorkbook.Close SaveChanges:=False

End Sub

Each time excel opens, it would open this workbook. The workbook would do its
stuff and close.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Carol]

Thanks, I'll give it a shot.



It's been my experience that excel likes to remember what you used the
previous
time (within the same excel session).

So once you do data|text to columns, excel will remember this when you paste
that comma delimited stuff the second time.

If you close the workbook, then excel forgets and goes back to its default.

I don't think you can change the default, but you can take advantage of
excel's
"helpfulness".

You could make a dummy workbook and put it in your xlStart folder. Have a
macro
in that workbook that does a texttocolumns the way you want. Then closes
and
gets out of the way.

Option Explicit
Private Sub Auto_Open()

With ThisWorkbook.Worksheets(1).Range("a1")
.Value = "asdf"
.TextToColumns Destination:=.Columns(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False
End With

ThisWorkbook.Close SaveChanges:=False

End Sub

Each time excel opens, it would open this workbook. The workbook would do
its
stuff and close.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm