-----Original Message-----
From: Arul, Rex [mailto:[EMAIL PROTECTED]]
Subject: Problem Converting a Timestamp in Excel
> Hello Friends,
> In the Excel spreadsheet, I am trying to cull out data, there
> is a column with values of this format: 22:15 (10:15:00 PM)
> However, while trying to get the data, I am getting decimal
> values of this format(for the same 10:15:00 PM): 0.927083333333333
> I am sure the Perl code is seeing it not as an object of OLE::Variant
> or any other Excel object type, as my code is returning nothing when
> I use the ref function for all the cells of the particular column. If
> it did, then I could have used the Value() function of OLE::Variant class.
> Please help.
> Thanks,
> Rex
I would say that it IS seing it as a variant type
Try forcing the type like so:
<CODE>
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE :TIME);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application');
$Excel->{DisplayAlerts}=0;
my $Book = $Excel->{ActiveWorkbook};
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();
my $dt = Variant(VT_DATE, $Sheet->Range("a2")->{Value});
## A2 has your decimal value, You can also input a time value,
## (10:22:54 pm) and it will work
print $dt->Time, "\n";
print $dt->Time(TIME_FORCE24HOURFORMAT|TIME_NOTIMEMARKER), "\n";
print $dt->Time("hh.mm.ss tt"), "\n";
</CODE>
The following is directly from the ::Variant docs
.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=
Converts the VARIANT into a formatted time string. FORMAT can be either
one of the following integer constants or a format string:
LOCALE_NOUSEROVERRIDE system default time format for this locale
TIME_NOMINUTESORSECONDS don't use minutes or seconds
TIME_NOSECONDS don't use seconds
TIME_NOTIMEMARKER don't use a time marker
TIME_FORCE24HOURFORMAT always use a 24-hour time format
The constants are available from the Win32::OLE::NLS module:
use Win32::OLE::NLS qw(:LOCALE :TIME);
The following elements can be used to construct a time format string.
Characters must be specified exactly as given below (e.g. "dd" B<not> "DD").
Spaces can be inserted anywhere between formating codes, other verbatim
text should be included in single quotes.
h hours; 12-hour clock
hh hours with leading zero for single-digit hours; 12-hour
clock
H hours; 24-hour clock
HH hours with leading zero for single-digit hours; 24-hour
clock
m minutes
mm minutes with leading zero for single-digit minutes
s seconds
ss seconds with leading zero for single-digit seconds
t one character time marker string, such as A or P
tt multicharacter time marker string, such as AM or PM
.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=.-=
Chuck Charbeneau
_______________________________________________
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
