Perl program to add 3 columns to an existing excel file and generate random values

At the moment I am working on XP/Vista, Perl is installed on this system as is Excel 2007.
I also have access to UNIX although this is limted and not available at present.

Thank you for that, although I have not got it working, Its probably something simple, I am currently getting the error
Can't call method "worksheets" on an undefined value at C:\.(path)\test.pl line 8

Sorry this is probably to with me being clueless at the moment for the most part when it comes to Perl.

What do you have on line 7?
Did you update it to have the path to your file?

line 7 has been updated to the path to my workbook
my $workbook = $xl_app->Workbooks->Open('/book1.xls');

Is that the correct path?

Replace line 7 with this:
my $workbook = $xl_app->Workbooks->Open('/temp_ee/test1.xls') or die "Could not open workbook: $!\n";

This is the full code used, I changed the path of workbook and the columns in which data will be generated

#!/usr/bin/perl -w
use strict;
use OLE;
 
my $xl_app = CreateObject OLE 'Excel.Application' || die $!;
$xl_app->{'Visible'} = 0;
my $workbook = $xl_app->Workbooks->Open('/book1.xls');
my $worksheet = $workbook->Worksheets(1);
 
$worksheet->Range("A1")->{Value} = 'ID';
$worksheet->Range("I1")->{Value} = 'Price';
$worksheet->Range("H1")->{Value} = 'Currentstock';
 
my $currow=2;
while($worksheet->Range("B$currow")->{Value}) {
	$worksheet->Range("A$currow")->{Value} = int(rand(9999)+1);
	$worksheet->Range("I$currow")->{Value} = int(rand(50)+1);
	$worksheet->Range("H$currow")->{Value} = int(rand(201));
	$currow++;
}
 
$workbook->Save();
 
$xl_app->ActiveWorkbook->Close(0);
$xl_app->Quit();

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

I replaced the line, and it is telling me it could not open workbook
The workbook is currently in the same directory as the perl program, does it need to be in a seperate directory?

If it is in the same directory, don't put a slash before the filename.

Line 7 should be:
my $workbook = $xl_app->Workbooks->Open('book1.xls') or die "Could not open workbook: $!\n";

Hi, thanks I tried that, but still it tells me it could not open workbook, I am not sure why, I have checked file permissions etc and see can think of nothing which should prevent workbook from being opened

Did you add the "or die" part to line 7?  What message do you get when you run it?

yes the or die has been added, the message i get when i try to run it is could not open workbook.
I tried a different perl program, which opens up, creates a new workbook and adds some values, this worked fine, although this does not help me, for some reason the existing workbook is unable to be opened by the perl program.

Try this:


....
my $FileName = 'book1.xls';      #NOTE: This line is new
my $xl_app = CreateObject OLE 'Excel.Application' || die $!;
$xl_app->{'Visible'} = 0;
die "File does not exist, or is not readable\n" unless -r $FileName;     #NOTE: This line is new
my $workbook = $xl_app->Workbooks->Open($FileName)    #NOTE: This line is changed
    or die "Could not open workbook: $!\n";    #NOTE: This line is new
my $worksheet = $workbook->Worksheets(1);
...

Thank you for trying to sort this problem, I entered the new code and same problem, could not open workbook.
I think your code is fine, and this may be an issue on my laptop, possibly with vista, although i have checked permissions.

I transferred the files to XP and still same problem of not being able to open the workbook

Can you open the file in excel?

The file opens no problem in excel on XP or Vista
I tried to make a new file, same name, but same problem exists that the perl program can not open workbook. I have tried it on 3 different computers, 1 running Vista, 2 running XP Professional, really has confused me.

Did you make the change I suggested in post 20329184?  
With that change, does anything different happen?
What is the exact error message you get?

That change has been made and the line is still could not open workbook

Try using the full path to the file:

my $FileName = 'c:\path\to\your\file\book1.xls';
...
my $workbook = $xl_app->Workbooks->Open($FileName)    #NOTE: This line is changed
    or die "Could not open workbook: $!\n";    #NOTE: This line is new

Still the same problem, thank you for your continuing efforts to help with this problem

After the "Could not open workbook", is there anything else displayed?

Try with this minor change also:

my $workbook = $xl_app->Workbooks->Open($FileName)
    or die "Could not open workbook:\n  $!\n  $@\n";

The only message is Could not open Workbook.
With the new change do I need to add a value to $FileName before trying to run the program?

Yes, set filename like so:
my $FileName = 'c:\path\to\your\file\book1.xls';

Same as before "Could not open workbook:"

Here is another change...


#!/usr/bin/perl -w
use strict;
use OLE;
 
my $FileName = 'c:\path\to\files\book1.xls';
my $xl_app = CreateObject OLE 'Excel.Application' || die "Could not create app: $!";
$xl_app->{'Visible'} = 0;
die "File does not exist, or is not readable\n" unless -r $FileName;
my $workbook = $xl_app->Workbooks->Open($FileName);
if($workbook) {
    print "Workbook is created\n";
}
else {
    print "Workbook NOT created:\n  $!\n  $@\n";
}
my $worksheet = $workbook->Worksheets(1);
 
$worksheet->Range("F1")->{Value} = 'ID';
$worksheet->Range("G1")->{Value} = 'Price';
$worksheet->Range("H1")->{Value} = 'Currentstock';
 
my $currow=2;
while($worksheet->Range("A$currow")->{Value}) {
      $worksheet->Range("F$currow")->{Value} = int(rand(9999)+1);
      $worksheet->Range("G$currow")->{Value} = int(rand(50)+1);
      $worksheet->Range("H$currow")->{Value} = int(rand(201));
      $currow++;
}
 
$workbook->Save();
 
$xl_app->ActiveWorkbook->Close(0);
$xl_app->Quit();

new message is workbook is created although the workbook still has not been altered after running the program

What is the output from this:

...
my $worksheet = $workbook->Worksheets(1);
print "A1 = " . $worksheet->Range("A1") . "\n";
print "B1 = " . $worksheet->Range("B1") . "\n";
print "A2 = " . $worksheet->Range("A2") . "\n";
print "B2 = " . $worksheet->Range("B2") . "\n";

Are those values what is actually in the worksheet?

I ran the code again, and it has worked. I am not sure what you changed in post 20353631 but it seems to of done the trick.
Thank you very much for your patience and help, really appreciated.