I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros? Thanks!

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

Use an apostrophe as the first character, i.e '01 rather than 01
/Fredrik

I tried adding a leading apostrophe and the zero is there but now how do I
get rid of the apostrophe? What is interesting is that if I use NumberFormat
for say currency on a column the formatting will stick. When I do a
NumberFormat = "@" then when I open the resulting spreadsheet, the formatting
is still general. If I can programmatically set the format to text, then
removing any extra leading character would be simple and problem solved. I
can't seem to get the text format to stick programmatically. Thanks!

"Fredrik Wahlgren" wrote:


"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

Use an apostrophe as the first character, i.e '01 rather than 01
/Fredrik

If a CSV, OpenText doesn't pay any attention to you settings. Change the
name of the file to have a .txt extension and use the settings in OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

I appreciate the help but my file already has a .txt extension, I have
international postal codes so the fixed format does not apply, and I have
tried the OpenText FieldInfo settings. The code does not error when I
formatnumber="@", but the column does not appear/stay formatted as text.
Same with the OpenText FieldInfo try. Thanks!

"Tom Ogilvy" wrote:

If a CSV, OpenText doesn't pay any attention to you settings. Change the
name of the file to have a .txt extension and use the settings in OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

Formatting does not change arbitrarily. Also, formatting a column after the
data is already there does nothing to the existing data. The zero is gone
at that time. It would be interesting to know how you determine if the
formatting is still there or not. Looking at it is not the correct answer.



--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I appreciate the help but my file already has a .txt extension, I have
international postal codes so the fixed format does not apply, and I have
tried the OpenText FieldInfo settings. The code does not error when I
formatnumber="@", but the column does not appear/stay formatted as text.
Same with the OpenText FieldInfo try. Thanks!

"Tom Ogilvy" wrote:

If a CSV, OpenText doesn't pay any attention to you settings. Change
the
name of the file to have a .txt extension and use the settings in
OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

If you manually right-click and format cell and save/click OK, then when you
go back to right-click the cell it will show the new formatting.

If I programmitically set the formatting then by right-clicking cells
format cells does not show that the cell is formatted as text.

Thanks!

"Tom Ogilvy" wrote:

Formatting does not change arbitrarily. Also, formatting a column after the
data is already there does nothing to the existing data. The zero is gone
at that time. It would be interesting to know how you determine if the
formatting is still there or not. Looking at it is not the correct answer.



--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I appreciate the help but my file already has a .txt extension, I have
international postal codes so the fixed format does not apply, and I have
tried the OpenText FieldInfo settings. The code does not error when I
formatnumber="@", but the column does not appear/stay formatted as text.
Same with the OpenText FieldInfo try. Thanks!

"Tom Ogilvy" wrote:

If a CSV, OpenText doesn't pay any attention to you settings. Change
the
name of the file to have a .txt extension and use the settings in
OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat = "@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading zeros?
Thanks!

If I programmitically set the formatting then by right-clicking cells
format cells does not show that the cell is formatted as text.

It does for me.

--
Regards,
Tom Ogilvy



"Salty Dog" wrote in message
...
If you manually right-click and format cell and save/click OK, then when
you
go back to right-click the cell it will show the new formatting.

If I programmitically set the formatting then by right-clicking cells
format cells does not show that the cell is formatted as text.

Thanks!

"Tom Ogilvy" wrote:

Formatting does not change arbitrarily. Also, formatting a column after
the
data is already there does nothing to the existing data. The zero is
gone
at that time. It would be interesting to know how you determine if the
formatting is still there or not. Looking at it is not the correct
answer.



--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I appreciate the help but my file already has a .txt extension, I have
international postal codes so the fixed format does not apply, and I
have
tried the OpenText FieldInfo settings. The code does not error when I
formatnumber="@", but the column does not appear/stay formatted as
text.
Same with the OpenText FieldInfo try. Thanks!

"Tom Ogilvy" wrote:

If a CSV, OpenText doesn't pay any attention to you settings.
Change
the
name of the file to have a .txt extension and use the settings in
OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat =
"00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat =
"@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading
zeros?
Thanks!

Got it. The resulting file was being saved back as the original file format
which stripped all of the, turns out, successful formatting.

Thanks for all your help!!!

"Tom Ogilvy" wrote:

If I programmitically set the formatting then by right-clicking cells
format cells does not show that the cell is formatted as text.

It does for me.

--
Regards,
Tom Ogilvy



"Salty Dog" wrote in message
...
If you manually right-click and format cell and save/click OK, then when
you
go back to right-click the cell it will show the new formatting.

If I programmitically set the formatting then by right-clicking cells
format cells does not show that the cell is formatted as text.

Thanks!

"Tom Ogilvy" wrote:

Formatting does not change arbitrarily. Also, formatting a column after
the
data is already there does nothing to the existing data. The zero is
gone
at that time. It would be interesting to know how you determine if the
formatting is still there or not. Looking at it is not the correct
answer.



--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I appreciate the help but my file already has a .txt extension, I have
international postal codes so the fixed format does not apply, and I
have
tried the OpenText FieldInfo settings. The code does not error when I
formatnumber="@", but the column does not appear/stay formatted as
text.
Same with the OpenText FieldInfo try. Thanks!

"Tom Ogilvy" wrote:

If a CSV, OpenText doesn't pay any attention to you settings.
Change
the
name of the file to have a .txt extension and use the settings in
OpenText
to tell excel to treat that column as Text.

If you can't get that to work, you can format the column as

objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat =
"00000"

or loop through each cell and do something like

cell.Vaue = "'" & Right("00000" & cell.Value,5)

--
Regards,
Tom Ogilvy

"Salty Dog" wrote in message
...
I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").N umberFormat =
"@"
and no luck.

I also tried the FieldInfo Array of Arrays for OpenText.

I'm using vbs and I'm looking for a way to not loose leading
zeros?
Thanks!