Setting Date Format in Excel - PB 8 vs. PB 9?

Tested in PB 9.02, B 7509.
Following works fine:

myoleobject.application.workbooks(1).ActiveSheet.Columns("D:L").NumberFormat
= "dd-mmm-yy"
   Pull up the spreadsheet in Excel and the dates appear in the correct
format, as: 25-Feb-05

However, the final app is in PB 8.03, B 9977.
Using the same script, the dates appear in Excel as 2/25/2005.
The weird thing is , even in Excel, it is not possible to change the format!
(Look at the 'format cells' dialog in Excel and it shows the format as
'Date' ,  '14-Mar-98' ,
  but the date is not in that format, and it cannot be changed.)

What gives?  Is this a problem with this build of PB 8.03?
Is there a fix or workaround?

Ken Metz
Kansas City Power & Light


0
Ken
9/20/2005 4:08:59 PM
sybase.ole-ocx-activex 6324 articles. 0 followers. Follow

1 Replies
594 Views

Similar Articles

[PageSpeed] 20
Get it on Google Play
Get it on Apple App Store

Well, I never got an answer, so I will post my workaround,
maybe it will help someone:

Just pass the dates as strings.
Once they get in Excel, Excel seems to be able to figure them out.

The other twist that I didn't mention here is that PB 8.03 only has Excel5!
as an option.
We are using Excel 2000.
If you save the DW as a spreadsheet from PB as Excel5!, then pull it into
Excel 2000,
it will ask you if you want to save in the new format. Can't have that in a
'batch' process.
So, in my script I do:
 myoleobject.Application.DisplayAlerts = False
 myoleobject.application.workbooks(1).close(True)
 myoleobject.Application.DisplayAlerts = True

This works, but the dates are formatted incorrectly, when I pass them as
dates
(i.e., the DW column is a date field).

Anyway, just sticking to strings works for PB 8.03.  (Passing as dates works
OK in PB 9.02.)


Ken

"Ken Metz" <kenneth.metz@kcpl.com> wrote in message
news:4330341b$1@forums-1-dub...
> Tested in PB 9.02, B 7509.
> Following works fine:
>
>
myoleobject.application.workbooks(1).ActiveSheet.Columns("D:L").NumberFormat
> = "dd-mmm-yy"
>    Pull up the spreadsheet in Excel and the dates appear in the correct
> format, as: 25-Feb-05
>
> However, the final app is in PB 8.03, B 9977.
> Using the same script, the dates appear in Excel as 2/25/2005.
> The weird thing is , even in Excel, it is not possible to change the
format!
> (Look at the 'format cells' dialog in Excel and it shows the format as
> 'Date' ,  '14-Mar-98' ,
>   but the date is not in that format, and it cannot be changed.)
>
> What gives?  Is this a problem with this build of PB 8.03?
> Is there a fix or workaround?
>
> Ken Metz
> Kansas City Power & Light
>
>


0
Ken
9/27/2005 2:32:29 PM
Reply: