Specify the read only attribute in my Perl code so I don’t have to click on read only

5 pts.
Microsoft Excel
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 "%sn", $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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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?

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 );

Discuss This Question:  

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: