Question

specify the read only attribute in my Perl code so i don't have to click on read only

Win32::OLE, Microsoft Excel, Perl

I have created a perl script that read from an excel file that is located on our windows server, the goal is to automate the perl script to run once a week and read the first 5 columns of the row that matches the current date and then write the data to a .txt file.

A bash script will then open that .txt file and retrieve the data (Names) and plug it into an email body and send it to a particular group.

I was able to finish both the bash script and the Perl script, however I have 2 issues that I am not able to resolve, and was hoping that you guys could help me?


The first issue:
I am using Win32::OLE to write the Perl script, that excel file is password protected and every time I run that script a dialog box pops up and I have to click on Read-Only then my script executes. Is there any way I can specify the read only attribute in my code so i don't have to click on read only?

The second issue:
The first column in the .xls file is a date and everytime teh Perl script read that date column Perl convert the date to a String.
Is there a way for me to read the date correctly (As a date) so I could tell my perl scrip to loop through all the rows and when date == CurrentDate retrieve that data?


#!/usr/bin/perl -w

#use DateTime::Format::Mail;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

#$DATE=`date +%D`;

$Win32::OLE::Warn = 3; # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); # get already active Excel
# application or open new

# open Excel file

my $file = "\\\\moe\\tech\\SWE\\SweTest.xls";
my $Book = $Excel->Workbooks->Open($file);

# You can dynamically obtain the number of worksheets, rows, and columns
# through the Excel OLE interface. Excel's Visual Basic Editor has more
# information on the Excel OLE interface. Here we just use the first
# worksheet, rows 1 through 4 and columns 1 through 3.

# select worksheet number 4 (you can also select a worksheet by name)
my $Sheet = $Book->Worksheets(4);

#if $row=$DATE{
foreach my $row (15) #date modified for April 02
{
foreach my $col (2..5)
{
# skip empty cells
next unless defined $Sheet->Cells($row,$col)->{'Value'};

# print out the contents of a cell
printf "%s\n",
$Sheet->Cells($row,$col)->{'Value'};

open (MYFILE, '>>/HOME/bash/data.txt')or die "Cannot open file: $!";
print MYFILE "\n",
$Sheet->Cells($row,$col)->{'Value'};
close (MYFILE);

}
}

# my $pf = DateTime::Format::Mail->new();
# print $pf->parse_datetime(
# "Fri, 23 Nov 2001 21:57:24 -0600"
# )->ymd; # "2001-11-23"


# clean up after ourselves
$Book->Close;

Thank you for all your help
-guy

I know Perl and I've worked some with VBA, so I can give this a shot -- but I haven't used Win32::OLE, so you may have to experiment with this a bit.

Question 1: How to open in read-only mode

The prototype for Workbooks.Open, from Microsoft's VBA reference is:
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

In VBA, you'd be able to do:
Workbooks.Open(fileName, ReadOnly:=True)

In Perl, you can either pass undef for UpdateLinks to pass it, or use a named parameter in a hash. So I'm guessing it would be something like:
my $Book = $Excel->Workbooks->Open(FileName => $file, ReadOnly=>1); #maybe?
This is according to Win32::OLE on CPAN.

So basically, if you set ReadOnly to true, I'm guessing it won't pop up an alert to open in read-only mode.

Question 2: Reading the date
In VBA, the string representation of a date cell always seems to be of mm/dd/yyyy format, regardless of how the cell's formatting is set. If this is the case, you can use a simple regex to convert that into a Perl-style date:

 # assume $dateStr has the date in string format, from OLE
 $dateStr =~ /(\d+)\/(\d+)\/(\d+)/; # matches d+/d+/d+
 $dateMonths = $1;
 $dateDays = $2;
 $dateYears = $3;
 
 $date = timelocal(0, 0, 0, $dateDays, $dateMonths , $dateYears );