Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 7th 05, 04:01 PM
Bruce Bowler
 
Posts: n/a
Default Text import wizard defaults?

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
|
+-------------------+---------------------------------------------------+


  #2   Report Post  
Old February 8th 05, 12:14 AM
Dave Peterson
 
Posts: n/a
Default

Saved from a previous post:

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

Bruce Bowler wrote:

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
|
+-------------------+---------------------------------------------------+


--

Dave Peterson
  #3   Report Post  
Old February 8th 05, 04:56 PM
Bruce Bowler
 
Posts: n/a
Default

Might work for pasted stuff, using datatext to columns but that's not
what I'm doing (and, in fact, cant easily do).

And excel doesn't remember the settings for the text import wizard between
imports in the same excel session.

Oh well, I guess it's off to write a macro to handle what should be a
natively supported operation (but instead Billy boy decided to pay his
developers to put a flight simulator in excel... Yep, makes sense to me :-)

Bruce

On Mon, 07 Feb 2005 17:14:30 -0600, Dave Peterson put fingers to
keyboard and said:

Saved from a previous post:

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

Bruce Bowler wrote:

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
|
+-------------------+---------------------------------------------------+


--
+-------------------+---------------------------------------------------+
Bruce Bowler | History repeats itself. That's one of the things
1.207.633.9600 | wrong with history. - Clarence Darrow
|
+-------------------+---------------------------------------------------+

  #4   Report Post  
Old February 8th 05, 11:58 PM
Dave Peterson
 
Posts: n/a
Default

This technique has always worked for me when I do file|open or data|text to
columns.

And excel has always remembered the settings until I either closed excel or ran
a superseding different text to columns.

I don't have a guess why it doesn't work for you.

Bruce Bowler wrote:

Might work for pasted stuff, using datatext to columns but that's not
what I'm doing (and, in fact, cant easily do).

And excel doesn't remember the settings for the text import wizard between
imports in the same excel session.

Oh well, I guess it's off to write a macro to handle what should be a
natively supported operation (but instead Billy boy decided to pay his
developers to put a flight simulator in excel... Yep, makes sense to me :-)

Bruce

On Mon, 07 Feb 2005 17:14:30 -0600, Dave Peterson put fingers to
keyboard and said:

Saved from a previous post:

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

Bruce Bowler wrote:

Is it possible to change the text import wizard defaults in excel 2003?

The goal is to change it so that it *always* picks delimited and to, by
default, have space be a "selected" delimiter.

None of the files I import are fixed width, although some of the "look"
that way to the import wizard, and all of them use spaces as delimiters.

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | As the economy gets better, everything else gets
1.207.633.9600 | worse. - Buchwald's Law
|
+-------------------+---------------------------------------------------+


--
+-------------------+---------------------------------------------------+
Bruce Bowler | History repeats itself. That's one of the things
1.207.633.9600 | wrong with history. - Clarence Darrow
|
+-------------------+---------------------------------------------------+


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I import XML as text format Huw Excel Discussion (Misc queries) 1 July 28th 06 04:09 PM
Import comma delimited text Lin Excel Discussion (Misc queries) 3 July 28th 06 04:07 PM
I have a Comma delimited text file how do I import in to excel an. trevord Excel Discussion (Misc queries) 1 February 4th 05 12:41 AM
Text import out of line? Krefty Excel Discussion (Misc queries) 3 December 21st 04 06:08 PM
VBA Import of text file & Array parsing of that data Dennis Excel Discussion (Misc queries) 4 November 28th 04 11:20 PM


All times are GMT +1. The time now is 03:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017