Hello all, I am a new Perl programmer. Below is the beginnings of a code that I am using to manipulate an Excel spreadsheet via Perl using win32::OLE. However, what I'd like to do is the following: instead of either opening a file or creating a new one, I'd like to do BOTH. I was thinking of creating a loop where I could basically say something like:
if $excelfile exists, then open $excelfile, otherwise, create a new workbook named $excelfile
However, being new to Perl, I haven't quite been able to find the correct syntax. Can you provide any clues as to how to do this, or is it even possible?
Thanks! ;) - ------------------------------------------------------------
-
#!/usr/bin/perl -w
-
-
use strict;
-
use Win32::OLE qw(in with);
-
use Win32::OLE::Const 'Microsoft Excel';
-
-
$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');
-
-
my $excel_file = 'C:/Perl/myfolder/testbook';
-
-
# open Excel file
-
my $Book = $Excel->Workbooks->Open("$excel_file");
-
------------------------------------------------------------
-
3 41750
Hey,
You are almost there, all you need is the code for the if file exists / else: -
#!/usr/bin/perl -w
-
-
use strict;
-
use Win32::OLE qw(in with);
-
use Win32::OLE::Const 'Microsoft Excel';
-
-
$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');
-
-
my $excel_file = 'C:/Perl/myfolder/testbook';
-
-
// -e checks to see if the file exists
-
if (-e "$excel_file")
-
{
-
# open Excel file
-
my $Book = $Excel->Workbooks->Open("$excel_file");
-
# run any other excel code you want here
-
# ...
-
}else{
-
// paste code here to create a new file
-
$excel->{Visible} = 1;
-
$excel->{SheetsInNewWorkBook} = 1;
-
$workbook = $excel->Workbooks->Add();
-
$worksheet = $workbook->Worksheets(1);
-
$worksheet->{Name} = "New WorkSheet";
-
-
# run any other excel code you want here
-
# ...
-
-
$workbook->SaveAs($excel_file);
-
}
-
I had the same question. this was about link #40 hat i opened looking for help and the first one to go beyond giving mee how to open an excel file.
I have two more questions though:
1: how do i find the last row number with information so that I may jump to there?
2: how do i get this to actually fill in and save? when i run my script now it has 2 books with blank sheets.
my wrapper file (batch), with computer names removed for safety/brevity: - @echo on
-
rem this batch is for cataloguing computers using two other "helpers"
-
rem "helpers" are psinfo and compinfo
-
rem information section is over, turning echo off.
-
@echo off
-
-
compinfo -l l1.txt -c [a bunch of computer names] >> errlog.txt
-
compinfo -l l2.txt -c [a bunch of computer names] >> errlog.txt
-
excerpt from my perl file that has the interesting parts of excel interaction - #! /usr/bin/perl
-
use strict;
-
use warnings;
-
use Getopt::Long; # used in getting unix-style options
-
use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
-
$Win32::OLE::Warn = 3; # die on errors...
-
-
# some stuff that's probably not interesting nor relevant
-
-
sub rep{
-
# this assumes two passed values,
-
# first a message
-
# second a flag for verbose (screen printing)
-
my $time=localtime().' | ';
-
print LOG "$time $_[0]\n";
-
if($_[1]){ print STDOUT "$time $_[0]\n"; }
-
}
-
-
# stuff creating files of information used to create the excel spreadsheet
-
-
#######
-
## OLE interaction upon the saved files follows
-
#######
-
-
# get an active Excel or create a new one
-
my $Excel = Win32::OLE -> GetActiveObject('Excel.Application')
-
|| Win32::OLE -> new('Excel.Application', 'Quit');
-
-
my $labrep="./Lab_Report_$date.xls";
-
-
# does the file exit?
-
if(-e "$labrep"){
-
#we are just adding to it, so open it
-
my $report = $Excel->Workbooks->Open("$labrep");
-
my $ws = $report -> Worksheets(1);
-
-
my $ldate = localtime();
-
&rep("Examining information gathered from lab computers @ $ldate\n", $verb);
-
-
# start row counter
-
my $row=2;##<----bad! needs to jump to end!!!!!
-
-
###
-
#some stuff like below only without the first row and should be after what's there
-
###
-
-
}else{
-
# we have to create it, including make the first row
-
$Excel -> {'Visible'} = 1;
-
$Excel -> { 'SheetsInNewWorkBook' } = 1;
-
my $workbook = $Excel -> Workbooks -> Add();
-
my $ws = $workbook -> Worksheets(1);
-
$ws -> { 'Name' } = "Lab Report $date";
-
-
# set first row titles
-
$ws -> Cells(1, "A") -> ('Value') = "Node";
-
$ws -> Cells(1, "B") -> ('Value') = "NAV";
-
$ws -> Cells(1, "C") -> ('Value') = "Alarms";
-
$ws -> Cells(1, "D") -> ('Value') = "SNMP";
-
$ws -> Cells(1, "E") -> ('Value') = "Uptime";
-
$ws -> Cells(1, "F") -> ('Value') = "Kernel Version";
-
$ws -> Cells(1, "G") -> ('Value') = "Product Type";
-
$ws -> Cells(1, "H") -> ('Value') = "Product Version";
-
$ws -> Cells(1, "I") -> ('Value') = "Service Pack";
-
$ws -> Cells(1, "J") -> ('Value') = "Kernel Build Number";
-
$ws -> Cells(1, "K") -> ('Value') = "Registered Organization";
-
$ws -> Cells(1, "L") -> ('Value') = "Registered Owner";
-
$ws -> Cells(1, "M") -> ('Value') = "Install Date";
-
$ws -> Cells(1, "N") -> ('Value') = "Activation Status";
-
$ws -> Cells(1, "O") -> ('Value') = "IE Version";
-
$ws -> Cells(1, "P") -> ('Value') = "System Root";
-
$ws -> Cells(1, "Q") -> ('Value') = "Processors";
-
$ws -> Cells(1, "R") -> ('Value') = "Processor Speed";
-
$ws -> Cells(1, "S") -> ('Value') = "Processor Type";
-
$ws -> Cells(1, "T") -> ('Value') = "Physical Memory";
-
$ws -> Cells(1, "U") -> ('Value') = "Installed OS Hotfixes";
-
$ws -> Cells(1, "V") -> ('Value') = "Other Applications";
-
-
my $ldate = localtime();
-
&rep("Examining information gathered from lab computers @ $ldate\n", $verb);
-
-
# start row counter
-
my $row=2;
-
-
foreach my $node (@comps){
-
# for each node we check the information returned
-
# if there is no information we only have two items to place on the row,
-
# otherwise we have A-V items
-
-
# set the first cell since that's always going to be the same
-
$ws -> Cells($row, "A") -> ('Value') = "$node";
-
-
# try to open the file with the information
-
open LNINFO, "<$comp-$date.txt" or $err=1;
-
-
if($err){
-
# there was an error on the file, so note that in the report
-
$ws -> Cells($row, "B") -> ('Value') =
-
"$comp-$date.txt could not be opened to be put into the lab report. $! $^E\n";
-
-
}else{
-
# there was no error, so now we check the file,
-
# first reading in the file
-
my @examine;
-
while(<LNINFO>){
-
push @examine, $_;
-
}
-
-
# then noting which couldnt be connected to
-
if($examine[6] =~ m/The network path was not found./i ){
-
$ws -> Cells($row, "B") -> ('Value') = "The network path was not found.";
-
-
}else{
-
# we need to run through the file for the information to put into the rows
-
-
# drop what's before what we need
-
while (!($examine[0] =~ m/Uptime.*/i)){ shift(@examine); }
-
-
while (!($examine[0] =~ m/OS Hot Fix.*/i)){
-
# now go through a series of if/elsif sections for the rest
-
# hot fixes and programs will be handled slightly differently though
-
-
if($examine[0] =~ m/Uptime:\s+(\w.*)/i){
-
$ws -> Cells($row, "E") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Kernel version:\s+(\w.*)/i){
-
$ws -> Cells($row, "F") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Product type:\s+(\w.*)/i){
-
$ws -> Cells($row, "G") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Product version:\s+(\w.*)/i){
-
$ws -> Cells($row, "H") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Service pack:\s+(\w.*)/i){
-
$ws -> Cells($row, "I") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Kernel build number:\s+(\w.*)/i){
-
$ws -> Cells($row, "J") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Registered organization:\s+(\w.*)/i){
-
$ws -> Cells($row, "K") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Registered owner:\s+(\w.*)/i){
-
$ws -> Cells($row, "L") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Install date:\s+(\w.*)/i){
-
$ws -> Cells($row, "M") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Activation status:\s+(\w.*)/i){
-
$ws -> Cells($row, "N") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/IE version:\s+(\w.*)/i){
-
$ws -> Cells($row, "O") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/System root:\s+(\w.*)/i){
-
$ws -> Cells($row, "P") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Processors:\s+(\w.*)/i){
-
$ws -> Cells($row, "Q") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Processor speed:\s+(\w.*)/i){
-
$ws -> Cells($row, "R") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Processor type:\s+(\w.*)/i){
-
$ws -> Cells($row, "S") -> ('Value') = "$1";
-
}elsif($examine[0] =~ m/Physical memory:\s+(\w.*)/i){
-
$ws -> Cells($row, "T") -> ('Value') = "$1";
-
}
-
}
-
-
# remove the line starting "OS Hot Fix..."
-
shift(@examine);
-
# create a variable for holding the hotfixes
-
my $hotfixes="\"";
-
-
while (!($examine[0] =~ m/Applications.*/i)){
-
# while hot fixes need to be added. skip blank lines
-
if($examine[0] =~ m/\w/){
-
chomp($examine[0]);
-
$hotfixes .="$examine[0]\n";
-
}
-
}
-
-
# add the hotfixes
-
$ws -> Cells($row, "U") -> ('Value') = "$hotfixes";
-
-
# remove the line starting "Applications..."
-
shift(@examine);
-
-
my $apps='';
-
foreach my $app (@examine){
-
# the rest should all be applications to add
-
# so we're using a foreach to put them in
-
# only add lines that are not blank
-
# there are 3 special lines: NAV, Alarms, and SNMP
-
if($app =~ m/Symantec AntiVirus (\w+)/i){
-
$ws -> Cells($row, "B") -> ('Value') = "ver: $1";
-
}elsif($app =~ m/Alarm/i){
-
$ws -> Cells($row, "C") -> ('Value') = "Installed";
-
}elsif($app =~ m/SNMP Informant Agent \(([^\)]+)\) (\w.*)/i){
-
$ws -> Cells($row, "D") -> ('Value') = "$1 : $2";
-
}elsif($app =~ m/\s+(\w.*)/i){
-
$apps .= "$1\n";
-
}
-
}
-
-
# add the other applications
-
$ws -> Cells($row, "V") -> ('Value') = "$apps";
-
}
-
}
-
-
# end the loop increasing the row number
-
$row++;
-
}
-
-
$workbook -> SaveAs($labrep); # save active sheet
-
}
-
-
# save and exit
-
$Excel -> Workbooks -> Save(); # save file
-
$Excel -> Workbooks -> Quit(); # leave excel
-
my $et=locatime();
-
&rep("program completed at $et.",$verb); # wrap up log
-
close LOG; # close log
-
the script "finishes" but i never get the completed line in the log file, nor does the excel have any information, nor is there anything in the errlog.txt file.
the reg log only has the print out i would expect showing one how far the script is.
hi,
just came across this thread thought u might help me out
i tried to do it yr way..
but when i created a file already
it should input a hi at the position i stated
but it didn't...
is there anything wrong with my code? -
use Win32::OLE;
-
-
# use existing instance if Excel is already running
-
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
-
die "Excel not installed" if $@;
-
unless (defined $ex) {
-
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
-
or die "Oops, cannot start Excel";
-
}
-
-
if (-e "C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls")
-
-
{$book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls");
-
$sheet->Cells(3,1)->{Value} = ['hi'];
-
-
}else{
-
-
# get a new workbook
-
$book = $ex->Workbooks->Add;
-
$sheet = $book->Worksheets(1);
-
-
# write a 2 rows by 3 columns range
-
-
$sheet->Range("A1:J2")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],
-
[$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
-
-
-
$sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
-
[$max_pk_msg,$pk_msg]];
-
-
-
foreach(@parameters)
-
{
-
$sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
-
$sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
-
}
-
-
-
# print "XyzzyPerl"
-
$array = $sheet->Range("A3:I1")->{Value};
-
for (@$array) {
-
for (@$_) {
-
print defined($_) ? "$_|" : "<undef>|";
-
}
-
print "\n";
-
}
-
-
# save and exit
-
-
$book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
-
undef $book;
-
undef $ex;
-
}
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Luis Ferrao |
last post by:
Hi,
My Windows Forms application uses the WebBrowser control to
open an Excel file since OLE objects are gone in .Net. The application
is actualy a clone of the one found in the MS Knowledge...
|
by: JBAdamsJr |
last post by:
I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to...
|
by: shaurya.rastogi |
last post by:
I need to open an Excel file that has been stored in the Access
Database using the insert Object functionality of MSAccess manually.
What i am aware of is that i cant just read the field...
|
by: shaurya |
last post by:
I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MSAccess manually.
What i am aware of is that i cant just read the field...
|
by: marle |
last post by:
Hi all,
Can anyone briefly explain how to Open an excel file using OLE?
marle..
|
by: shintu |
last post by:
Hallo,
I am trying to write french accented characters é è ê in Excel worksheet using my perl script , But I am stuck here as I couldnt find a way of writing it !:
My code:
use strict;...
|
by: mohanprasadgutta |
last post by:
Hi,
I need help to open a password protected excel file in perl using Win32:OLE.
when I tried to open file in normal way at the time of program execution it is prompting me to enter password.
I am...
|
by: poolboi |
last post by:
hi all,
i've got the following program that needs yr help:
use Win32::OLE;
# use existing instance if Excel is already running
eval {$ex =...
|
by: Prasanna CRN |
last post by:
I am a Perl Programmer & i am using WIN32::OLE for Excel operations.
I wanted to use conditional formulas like 'if' in excel via Perl.
Thanks in Advance.
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |