Reading Date Formatted Excel Cell

merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks
I want to read an Excel spreadsheet cell
a) that references another cell in the spreadsheet for example
='DataSheet'!AH39;
b) where both cells have a date format for the cell.
I am using the line
$date_req = $cell_array->[$cell_row][$cell_column];
I get from this the result "Win32::OLE::Variant=SCALAR(0x29093f8)"
What should I do to get the date I require?

Comment on Reading Date Formatted Excel Cell Download Code

Replies are listed 'Best First'.
Re: Reading Date Formatted Excel Cell by Joost (Canon) on Mar 05, 2007 at 19:12 UTC
"Win32::OLE::Variant=SCALAR(0x29093f8)" means you've got a Win32::OLE::Variant object, not a number or string value. reading the docs you should probably use the Date() method: `print $date_req->Date("ddd',' MMM dd yy"), "\n";` [download] I don't have a win32 w/ perl available to me at the moment, so I haven't tested this. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^2: Reading Date Formatted Excel Cell by merrymonk (Hermit) on Mar 05, 2007 at 19:31 UTC
That worked and gave 'Thu, Mar 01 07' which is correct I should also have said that I wanted it in the form dd/mm/yy that is 01/03/07 Therefore I changed this to >Date("dd/mm/yy") This nearly worked since it gave 01/mm/07 What should mm be replaced by?	[reply]
Re^3: Reading Date Formatted Excel Cell by Joost (Canon) on Mar 05, 2007 at 20:13 UTC
I'm not sure, since I can't test it, but my guess would be "MM" instead of "mm". It should be in the docs (linked in my post above) "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re^4: Reading Date Formatted Excel Cell by merrymonk (Hermit) on Mar 05, 2007 at 20:29 UTC
Re^4: Reading Date Formatted Excel Cell by merrymonk (Hermit) on Mar 07, 2007 at 08:30 UTC