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:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
