OpenText looses leading zeros for zip

[Guest]

I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").NumberFormat = "@"
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!

 

[Fredrik Wahlgren]

I've tried
objExcel.ActiveWorkbook.Activesheet.Columns("L").NumberFormat = "@"
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

 

[Guest]

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!

 

[Tom Ogilvy]

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").NumberFormat = "00000"

or loop through each cell and do something like

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

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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