a question regarding cell format

[Kelie]

Hello,

Quite often I create tab-separated text file and open it in Excel. The
first column is usually "001", "002", "003", etc.. The problem is Excel
always automatically change them to "1", "2", "3", etc. which gives me
trouble. I've been looking in Excel trying to figure out how to
disable it but so far no luck. I realize this is not really a
programming question, but this is the group I visit about Excel.
Thanks in advance for any help.

Kelie

 

[Tom Ogilvy]

When you go through the text import wizard, in the last dialog, there is an
option to tell excel how to treat each column. You can specify that the
column be treated as Text.

 

[Kelie]

Thank you Tom. That fixed the problem. These text files all have .tsf
extension and all columns are text only. I associated this file format
(.tsf) with Excel so that I can open it in Excel by double clicking it.
But when doing this, I don't have the option to tell Excel to treat
columns as text. Is there a way to fix this? Btw, if there is no fix,
I can just open the file from Excel. Not a big trouble. Thanks
again. - Kelie

 

[Tom Ogilvy]

In the file associations dialog, you can provide a dde command to be used by
the open command. Excel DDE commands are the old xl4 macro commands. The
xl4 macro command associated with the text import wizard is the Open.Text
command. You can download the xl4 help file and build an open.text command
that propertly interprets your file. Here is a posting by Okaizawa in
response to a similar question:

Can i use something special in "use dde" fields of the explorer "Edit
file type" ? or modifying some windows registry keys ?

[open( )] in 'DDE Message' is a excel4 macro function.
the reference is here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd
http://support.microsoft.com/kb/143466/EN-US/

define 'open' action for your file type, for example:
(only 'DDE Message' is customized. others are the same for .xls)

Application used perform action:
".....\EXCEL.EXE" /e %1

[v]Use DDE (check)

DDE Message:
[open("%1",,, 6,,,,, "|")]
or
[open.text("%1",,,,,,0,0,0,0,1,"|")]

Application:
Excel

DDE Application Not Running:


Topic:
system

--
HTH,

okaizawa

----------------------------
That particular question was about using the | as the delimiter instead of a
column, but open.text also supports an array as the last argument that can
be used to indicate treating each column as text.

You can download the xl4 macro help files:

http://office.microsoft.com/downloads/2000/Xlmacro.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;128185&Product=xlw
Macrofun.exe File Available on Online Services
xl97 (old winhelp format)

http://support.microsoft.com/default.aspx?scid=kb;en-us;143466&Product=xlw
XL97: Macro97.exe File Available on Online Services
xl97 (old winhelp format)

 

[Kelie]

Thanks a lot Tom. I'm checking it out. - Kelie

In the file associations dialog, you can provide a dde command to be used by
the open command. Excel DDE commands are the old xl4 macro commands. The
xl4 macro command associated with the text import wizard is the Open.Text
command. You can download the xl4 help file and build an open.text command
that propertly interprets your file. Here is a posting by Okaizawa in
response to a similar question:

Can i use something special in "use dde" fields of the explorer "Edit
file type" ? or modifying some windows registry keys ?

[open( )] in 'DDE Message' is a excel4 macro function.
the reference is here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd
http://support.microsoft.com/kb/143466/EN-US/

define 'open' action for your file type, for example:
(only 'DDE Message' is customized. others are the same for .xls)

Application used perform action:
".....\EXCEL.EXE" /e %1

[v]Use DDE (check)

DDE Message:
[open("%1",,, 6,,,,, "|")]
or
[open.text("%1",,,,,,0,0,0,0,1,"|")]

Application:
Excel

DDE Application Not Running:


Topic:
system

--
HTH,

okaizawa

----------------------------
That particular question was about using the | as the delimiter instead of a
column, but open.text also supports an array as the last argument that can
be used to indicate treating each column as text.

You can download the xl4 macro help files:

http://office.microsoft.com/downloads/2000/Xlmacro.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;128185&Product=xlw
Macrofun.exe File Available on Online Services
xl97 (old winhelp format)

http://support.microsoft.com/default.aspx?scid=kb;en-us;143466&Product=xlw
XL97: Macro97.exe File Available on Online Services
xl97 (old winhelp format)

--
Regards,
Tom Ogilvy

Thank you Tom. That fixed the problem. These text files all have .tsf
extension and all columns are text only. I associated this file format
(.tsf) with Excel so that I can open it in Excel by double clicking it.
But when doing this, I don't have the option to tell Excel to treat
columns as text. Is there a way to fix this? Btw, if there is no fix,
I can just open the file from Excel. Not a big trouble. Thanks
again. - Kelie