Script works on WinXp & win98 but not Win2000

Jargo has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I've recently written my first script to take form data and update the stats for a squash ladder here at my university. When I test it out on my computer (both at work (XP) and at home(98)) using Xerver as the web server everything runs fine. But I brought it over to my colleague's computer today, he's using Win2000 with just the IIS or something or other as his web server and it can't open the excel spreadsheet with the data, and when it did manage to open the file it couldn't use the form data for some reason... Here is the code I was using:

#use strict;
use Win32::OLE::Const 'Microsoft Excel';
use CGI;

#get the data from the form
my $q = new CGI;

my $matchDay = $q->param(matchDay);
my $matchMonth = $q->param(matchMonth);
my $matchYear = $q->param(matchYear);
my $challenger = $q->param(challenger);
my $cRow = $q->param(cRow);
my $opponent = $q->param(opponent);
my $oRow = $q->param(oRow);
my $g1c = $q->param(g1c);
my $g1o = $q->param(g1o);
my $g2c = $q->param(g2c);
my $g2o = $q->param(g2o);
my $g3c = $q->param(g3c);
my $g3o = $q->param(g3o);
my $g4c = $q->param(g4c);
my $g4o = $q->param(g4o);
my $g5c = $q->param(g5c);
my $g5o = $q->param(g5o);

#open excel
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32:
+:OLE->new('Excel.Application', 'Quit');  

#open the spreadsheet...  used to use visible=1 for my own
#editing purposes
$Excel->{'Visible'}=0;
my $book = $Excel->Workbooks->Open("D:\\andreas\\perl\\squash.xls") ||
+ die("Unable to open document ", Win32::OLE->LastError());

#add a temporary sheet to stick the form data (Sheet1) by
#default
my $worksheet = $book->Worksheets->Add;
$worksheet->Range(A1)->{'value'} = $matchDay;
$worksheet->Range(A2)->{'value'} = $matchMonth;
$worksheet->Range(A3)->{'value'} = $matchYear;
$worksheet->Range(A4)->{'value'} = $challenger;
$worksheet->Range(A5)->{'value'} = $cRow;
$worksheet->Range(A6)->{'value'} = $opponent;
$worksheet->Range(A7)->{'value'} = $oRow;
$worksheet->Range(A8)->{'value'} = $g1c;
$worksheet->Range(A9)->{'value'} = $g1o;
$worksheet->Range(A10)->{'value'} = $g2c;
$worksheet->Range(A11)->{'value'} = $g2o;
$worksheet->Range(A12)->{'value'} = $g3c;
$worksheet->Range(A13)->{'value'} = $g3o;
$worksheet->Range(A14)->{'value'} = $g4c;
$worksheet->Range(A15)->{'value'} = $g4o;
$worksheet->Range(A16)->{'value'} = $g5c;
$worksheet->Range(A17)->{'value'} = $g5o;

#run excel macros that adds the new data
$Excel->Run("Start");

#remove the temporary sheet
$Excel->{'DisplayAlerts'}=False;
$worksheet->Delete;
$Excel->{'DisplayAlerts'}=True;

#run the macro in excel that generates the rankings
my $worksheet = $book->Worksheets("Summary");
$worksheet->Select;
$Excel->Run("updateStats");

$book->Save;
$book->Close;
[download]

I understand that I will probably have to add in some extra things to stop malicious use or whatever but I'm just trying to get this part to work first. Thanks, Andreas

Comment on Script works on WinXp & win98 but not Win2000 Download Code

Replies are listed 'Best First'.
Re: Script works on WinXp & win98 but not Win2000 by NetWallah (Canon) on Oct 10, 2003 at 05:14 UTC
"for some reason" is not good enough. If you need assistance, please provide an error message and/or a better description of the symptoms. You have a good start here, since you have posted the code, and your code does include some reasonable error-checking - how about posting the messages produced. Look at the event log, and the IIS log. Also, you need to be aware that your problem is most like not perl-related, and hence is off-topic here. There is a fairly high tolorence of off-topic stuff here, but try to provide sufficient info so the subject can be closed rapidly.	[reply]
Re: Re: Script works on WinXp & win98 but not Win2000 by Jargo (Initiate) on Oct 10, 2003 at 15:11 UTC
My apologies NetWallah. I realize that it might not be something inherently wrong with my "perl" code but perhaps other people had had similar problems opening excel files using the windows server. I am not sure how to get the IIS log or the event log (I mentioned before that I am a newbie at this stuff). From memory though, the first error that popped up had to do with the first macro that I run. My perl code (theoretically) adds a worksheet called "Sheet1" (by default) and puts the form data into that sheet but on my friends computer the script succeeded in creating "Sheet1" but then failed to put any of the form data into that sheet. My macros then got messed up because there was no content in Sheet1. After trying to change a few things, we ran clicked Submit again and every time I got the "Unable to Open document" error that was built into the script but it said it was opening document "0" and not the one I had written into the code. I thought that it might at first be something in the way perl is handled on different servers or OS's but if it has strayed too far from the topics on this site, I would appreciate a nudge in the right direction to find someone who might be able to help in this. My thoughts are that it might be something set in the IIS that does not let me change that xls file or something but that is just a guess. Thank you Andreas	[reply]
Re: Script works on WinXp & win98 but not Win2000 by inman (Curate) on Oct 10, 2003 at 16:15 UTC
Look at the following: Try your script from the command line. It will be easier to debug from here and make sure that Perl is installed. This will reveal any problems related to accessing Excel via OLE. Try a simple cgi script that doesn't require any other modules. This will make sure that Perl and IIS are happy. Try your script again. Start thinking about user rights etc. These operate at the file level and the IIS setup level. It is quite possible that on a corporate PC, the NTFS permissions have been tightened up. Inman	[reply]
Re: Script works on WinXp & win98 but not Win2000 by NetWallah (Canon) on Oct 10, 2003 at 18:52 UTC
Thanks for adding some detail. You need to add some more error-checking. I suspect that your $Excel object never gets created right. You can do something like this after creating it: `$Excel or die "Excel object not created\n";` [download] If you get that error, you have an Excel COM-object registration problem. The other issue is that the file and path to the workbok are hard-coded, and you don't check for file existance before opening it. Add this before opening the file: `die "Missing excel Workbook" unless -e "D:\\andreas\\perl\\squash.xls +";` [download] You should also check the contents of the form, and complain to the user if they are empty. This is not just for "user friendly"-ness - it can also help diagnose cases where your script is called from an unexpected place, without passing form info. This is just a start toward writing "good" code - there are several other programming technequies in your code that need improvement - this is just enough info to get you started.	[reply] [d/l] [select]
Re: Re: Script works on WinXp & win98 but not Win2000 by Anonymous Monk on Oct 11, 2003 at 20:21 UTC
Thank you NetWallah and Inman. I really appreciate the help. I will add some more error-trapping and see where the problem is arising and try to sort through this. I imagine I also have to set the permissions as Inman mentioned so I'll have to sit down with my buddy and sort out his computer early next week. Thanks, Andreas	[reply]