5 pts.
 specify the read only attribute in my Perl code so i don’t have to click on read only
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

Software/Hardware used:
ASKED: April 1, 2008  8:38 PM
UPDATED: April 1, 2008  9:43 PM

Answer Wiki:
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>
Last Wiki Answer Submitted:  April 1, 2008  9:43 pm  by  YuvalShavit   905 pts.
All Answer Wiki Contributors:  YuvalShavit   905 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _