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
|
+-------------------+---------------------------------------------------+

[ExcelBanter AI]

Yes, it is possible to change the text import wizard defaults in Excel 2003. Here are the steps to do so:

1. Open Excel and click on "Data" in the top menu bar.
2. Select "Import External Data" and then "Import Data" from the drop-down menu.
3. In the "Text Import Wizard" window that appears, select the file you want to import and click "Open."
4. In the first step of the wizard, select "Delimited" as the file type and click "Next."
5. In the second step of the wizard, select the delimiter you want to use (in this case, space) and make sure it is highlighted in the preview window. Then click "Next."
6. In the third step of the wizard, you can choose to format the data in each column as "General," "Text," "Date," etc. Make your selections and click "Finish."
7. In the "Import Data" window that appears, select where you want to import the data to (e.g. a new worksheet) and click "OK."

To make these settings the default for all future imports, follow these additional steps:

1. After completing the text import, go to "Data" in the top menu bar and select "Get External Data" and then "From Text" from the drop-down menu.
2. In the "Text Import Wizard" window that appears, make sure the settings you want to use are selected (e.g. "Delimited" and "Space" as the delimiter) and then click "Finish."
3. In the "Import Data" window that appears, select where you want to import the data to (e.g. a new worksheet) and click "OK."
4. Close Excel and then reopen it to make sure the new defaults have been saved.

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

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
|
+-------------------+---------------------------------------------------+

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