OpenText looses leading zeros for zip

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!

"Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> 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

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" <SaltyDog@discussions.microsoft.com> wrote in message
> news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> > 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
>
>
>

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)

--
Regards,
Tom Ogilvy

"Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> 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!
>
>

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").NumberFormat = "00000"
>
> or loop through each cell and do something like
>
> cell.Vaue = "'" & Right("00000" & cell.Value,5)
>
> --
> Regards,
> Tom Ogilvy
>
> "Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
> news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> > 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!
> >
> >
>
>
>

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" <SaltyDog@discussions.microsoft.com> wrote in message
news:69053F24-5923-45D6-A54A-559A952EFFDA@microsoft.com...
> 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").NumberFormat = "00000"
> >
> > or loop through each cell and do something like
> >
> > cell.Vaue = "'" & Right("00000" & cell.Value,5)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
> > news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> > > 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!
> > >
> > >
> >
> >
> >

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" <SaltyDog@discussions.microsoft.com> wrote in message
> news:69053F24-5923-45D6-A54A-559A952EFFDA@microsoft.com...
> > 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").NumberFormat = "00000"
> > >
> > > or loop through each cell and do something like
> > >
> > > cell.Vaue = "'" & Right("00000" & cell.Value,5)
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
> > > news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> > > > 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!
> > > >
> > > >
> > >
> > >
> > >
>
>
>

>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" <SaltyDog@discussions.microsoft.com> wrote in message
news:82D8DCCE-00CF-4520-8F75-F69BB34A5EDA@microsoft.com...
> 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" <SaltyDog@discussions.microsoft.com> wrote in message
> > news:69053F24-5923-45D6-A54A-559A952EFFDA@microsoft.com...
> > > 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").NumberFormat =
"00000"
> > > >
> > > > or loop through each cell and do something like
> > > >
> > > > cell.Vaue = "'" & Right("00000" & cell.Value,5)
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Salty Dog" <SaltyDog@discussions.microsoft.com> wrote in message
> > > > news:A57A1C58-F08D-40CA-B0E2-60AA14215363@microsoft.com...
> > > > > 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!
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >