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

5 pts.
Tags:
Microsoft Excel
Perl
Win32::OLE
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
ASKED: April 1, 2008  8:38 PM
UPDATED: April 1, 2008  9:43 PM

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.

<b>Question 1: How to open in read-only mode</b>

The prototype for Workbooks.Open, from <a href=”http://msdn2.microsoft.com/en-us/library/aa195811(office.11).aspx”>Microsoft’s VBA reference</a> is:
<i>expression</i>.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 <a href=”http://search.cpan.org/~jdb/libwin32-0.28/OLE/lib/Win32/OLE.pm”>Win32::OLE on CPAN</a>.

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

<b>Question 2: Reading the date</b>
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:

<pre>
# 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 );</pre>

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following