A while back I got a notice from Microsoft, saying that "editing" was being disabled on my copy of Office 2010. I had received this copy of Office while working for MOSTS (Microsoft Online Services Technical Support). ...but now that I had moved on to Blackberry support (where I am much happier) Microsoft felt I wasn't deserving of a non-crippled version of Office.
When I first read the aforementioned notice from MS, I thought they meant that my copy of Office had become read only, but I could still make entries in my MS Access databases, create e-mail with Outlook, etc. I recently found out that what they meant was I could no longer edit my MS Access databases via ODBC. This was really horrible news, because I use ODBC to communicate with my MS Access databases ; e.g., make entries through web page forms. The scripts that power my web pages are written in Perl, and make calls to ODBC, using the Perl module, DBI. This might sound very technical, but what it boils down to is my guestbook was no longer working (you could still view it, but you couldn't write to it).
Going back to earlier versions of MS Access was out of the question. Everything on Serval (this system) is 64 bit, too use a 32 bit version of MS Access would require a Herculean amount of work; the ODBC calls would have to be 32 bit (not a big deal to call WOW64), but getting 64 bit Perl to use the 32 bit modules is a serious task. ...but Perl offers a Win32::OLE module that works nicely with Excel. I have copies of Office 2000 and Office 2003. Office 2003 still presented some problems, but Office 2000 works nicely with my Perl scripts, after modifying them to use Excel instead of access.
I did take a performance hit after changing to Excel 2000. The guestbook page loads at a really slow rate. So, I broke up my guestbook with tabs. When you first bring up the guestbook you get the first 100 entries. Afterwards, if you click on the first tab you still get the first 100 entries. The second tab gives the next 100 entries, etc. This works out really great, since you never load more then 100 entries at a shot; it's always fast (relatively speaking). Here's the code:
#!C:\Perl\bin\perl.exe
use warnings;
# February 9, 2013. Replaced my unescape code with a regular experession - unescapes everything.
# February 5, 2013. I had originally copied and pasted my escape character code to wherever it was needed in this script.
# I just created a single subroutine for converting escape characters to display characters
# February 5, 2013. All my visitors seem to be making entries programatically, and putting their e-mail addresses
# in the phone field. I wrote a subroutine to check if the phone field is an e-mail address and
# if $email isn't an e-mail address; copy $phone over $email in this case.
#
# January 30, 2013. Read this interesting article at http://webdesign.about.com/od/layout/a/aa042307.htm
# The author, Jennifer Kyrnin, described how to use <ul > to split up a web page into tabs
# Here's what I need to do
#
# 1. Open up the Excel spreadsheet (read only)
# 2. Count the number of rows I have and then divide by 100 to get the number of html pages
# I need to create; add 1 if there is a remainder.
# 3. Create page #1, with tabs at the top for each page; send this page to the user
# 4. The tabs call the same page with different parameters; 0, 1, 2, ec.
use HTML::Entities qw(decode_entities);
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my $lcltmp = " ";
sub clean_escapes
{
$lcltmp = $_[0];
$lcltmp =~ tr/+/ /;
$lcltmp =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
return $lcltmp;
};
sub check_email
{
$lcltmp = $_[0];
if ($lcltmp =~ "\b[A-Z0-9+_.-]+@(?:[A-Z0-9-]+\.)+[A-Z]{2,6}\b")
{
return 1;
}
else
{
return 0;
}
};
$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 $spread = "c:\\Inetpub\\Scripts\\guestbook.xls";
# open Excel file, read only
my $Book = $Excel- >Workbooks- >Open($spread, 0, 1);
print "Content-type: text/html\n\n";
print " <html >";
print " <head >";
print " <title >Contents of Comments Database </title >";
print " <SCRIPT SRC=\"mktree4.js\" LANGUAGE=\"JavaScript\" >"." </SCRIPT >";
print " <LINK REL=\"stylesheet\" HREF=\"mktree.css\" >";
print " <LINK REL=\"stylesheet\" HREF=\"tabs.css\" >";
print " </head >";
print " <body bgcolor=\"#eeeeee\" id=\"homepage\" >";
print " <center > <h1 style=\"color: blue\" >Dave's Guest Book </h1 > </center >";
print " <br / >";
print " <div align=\"center\" > <A id=\"disabled\" href=\"#\" onClick=\"convertTrees(); return false;\" >Collapse Comments </A >
<A href=\"#\" onClick=\"expandTree('tree1'); return false;\" >Expand Comments </A >
<A href=\"#\" onClick=\"collapseTree('tree1'); return false;\" >Re-Collapse Comments </A > </div >";
print " <UL CLASS=\"mktree\" id=\"tree1\" >";
# select worksheet number 1
my $Sheet = $Book- >Worksheets(1);
close STDERR;
my $name = " ";
my $fname = " ";
my $mname = " ";
my $lname = " ";
my $phone = " ";
my $email = " ";
my $comments = " ";
my $phoneMail = 1;
my $row = 2;
my $col = 1;
my $Tot_Rows= $Sheet- >UsedRange- >Rows- >{'Count'};
my $Tab_Num = int($Tot_Rows/100);
# if there is a remainder then we need one more tab
my $remain = $Tot_Rows % 100;
if ($remain > 0)
{
$Tab_Num++;
}
# now that we know how many tabs we need, create the tabs at the top of the page; horizontally
my $cntr = 0;
my $tabStr = "tab";
my $tabVal = 0;
print " <ul id=\"tabs\" >";
for ($cntr = 0; $cntr < $Tab_Num; $cntr++)
{
$tabStr = "tab".$cntr;
if ($remain > 0)
{
if (($cntr + 1) * 100 > $Tot_Rows)
{
$tabVal = $cntr * 100 + $remain;
}
else
{
$tabVal = ($cntr + 1) * 100;
}
}
print " <li id=".$tabStr." >"." <a href=\"http://community-info.org/scripts/dsp7guests.pl?$cntr\" >".$tabVal." </a > </li >";
}
print " </ul > ";
print "There are ".$Tot_Rows." entries in this guestbook"." <br >"."*******************************"." <br >";
my $tabGroup = $ENV{QUERY_STRING};
$tabGroup++;
$tabGroup *= 100;
my $startVal = $tabGroup - 100;
if ($startVal == 0)
{
$startVal = 2;
}
foreach $row ($startVal..$tabGroup)
{
foreach $col (1..9)
{
# skip empty cells
next unless defined $Sheet- >Cells($row,$col)- >{'Value'};
# print out the contents of a cell
if ($col == 1)
{
$fname = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $fname;
$fname = &clean_escapes($_);
}
if ($col == 2)
{
$mname = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $mname;
$mname = &clean_escapes($_);
}
if ($col == 3)
{
$lname = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $lname;
$lname = &clean_escapes($_);
$name = $fname." ".$mname." ".$lname;
print "Name: $name\n";
print " <br >";
$fname = " ";
$mname = " ";
$lname = " ";
}
if ($col == 5)
{
$phone = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $phone;
$phone = &clean_escapes($_);
if (&check_email($phone))
{
# we have an e-mail address in the phone field
$phoneMail = 1;
}
else
{
$phoneMail = 0;
}
}
if ($col == 6)
{
$email = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $email;
$email = &clean_escapes($_);
if ((&check_email($email) == 0) && ($phoneMail))
{
$email = $phone;
}
print "Email Address: $email\n";
print " <br >";
$phone = " ";
$email = " ";
}
if ($col == 7)
{
$comments = $Sheet- >Cells($row,$col)- >{'Value'};
$_ = $comments;
$comments = &clean_escapes($_);
print " <br >";
if (length($comments) < 132)
{
print " <li style=\"color:DarkBlue\" >"."comments: ".$comments." </li >"."\n";
}
else
{
print " <li style=\"color:DarkBlue\" >"."comments: "." <ul >"." <li >".$comments." </li >"." </ul >"." </li >"."\n";
}
print "***********************************************************************\n";
print " <br > <br >";
}
$col++;
# print "The column number has just been inced to **********************: ".$col." <br >";
}
$row++;
# print "The row number has just been inced to **********************: ".$row." <br >";
}
print " </UL >";
# clean up after ourselves
$Book- >Close;
undef $Book;
undef $Excel;
print ' </body >';
print ' </html >';