error in opening of excel files using perl

[perl145]

use Win32::OLE qw(in with);

use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
my $Excel1 = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); # get already active Excel

#$Excel->{Visible} = 1;
print "file 1: ";
my $file1 = <STDIN>;
chomp($file1);
# application or open new
print "start of the column standard_address: ";
my $cs1 = <STDIN>;
print "end of the column standard_address: ";
my $ce1 = <STDIN>;
print "start of the column 'city': ";
my $city1 = <STDIN>;
print "end of the column 'city': ";
my $city2 = <STDIN>;
print "start of the column 'county': ";
my $cy1 = <STDIN>;
print "end of the column 'county': ";
my $cy2 = <STDIN>;
my $Book1 = $Excel1->Workbooks->Open($file1); # open Excel file
my $Sheet1 = $Book1->Worksheets(1); # select worksheet number 1
my $array1 = $Sheet1->Range("$cs1:$ce1")->{'Value'}; # get the contents
my $array11= $Sheet1->Range("$city1:$city2")->{'Value'};
my $array12= $Sheet1->Range("$cy1:$cy2")->{'Value'};
$Book1->Close;

The filename is to be entered dynamically when the code is running. After entering the filename which is stored in the same folder as the code I am getting an error:

OLE exception from "Microsoft Office Excel":

'Database.xls' could not be found. Check the spelling of the file name, and
verify that the file location is correct.

If you are trying to open the file from your list of most recently used
files, make sure that the file has not been renamed, moved, or deleted.

Win32::OLE(0.1709) error 0x800a03ec
in METHOD/PROPERTYGET "Open" at final2.pl line 36

Please help me. The file is stored with the correct name in the same folder.
Thanks

 

[rharsh]

I'm taking a wild guess here, but since you're using OLE to open Excel, the app might be looking in it's "Run In" folder and/or whatever the last folder it accessed (probably My Documets or somewhere else.) Have you tried opening a file with the full path?

Maybe try something like:

#my $file1 = <STDIN>;
my $file1 = 'Database.xls';

use File::Basename;
my $path = dirname($0);
$path =~ tr!\\!/!;

my $Book1 = $Excel1->Workbooks->Open("$path/$file1");

You might have to play with the \'s (I converted them to /'s which works most the time in Windows, but I don't know about OLE/Excel.)

 

[perl145]

Thanks..
But this too is not working...

 

[Annihilannic]

Does it work if you type in the full name of the file? i.e. C:\Some Folder\Database.xls

Annihilannic.

 

[perl145]

this too is not working...

 

[rharsh]

I just looked at your code again, it doesn't look like you're using chomp on your input. There's going to be an extra new-line character at the end of your file name. It probably wouldn't be a bad idea to check and make sure the file is even readable and/or exists before you try to open it with excel. Here are a couple functions you should take a look at:[ul][li]perldoc -f chomp[/li][li]perldoc -f -x[/li][/ul]

 

[chazoid]

the script works for me whether I use a forward slash or backslash. I get the same error message if I enter a nonexistent filename.
(I don't think the backslash acts as an escape when entered through stdin.)
You do need chomp()'s on the other inputs like rharsh said.

what do you see if you add this?

chomp( my $file1 = <STDIN>); # (you can chomp like this)
print "[$file1]\n";

 

[perl145]

still not working....

 

[Annihilannic]

Same error? As a test, are you able to open and read the file from your perl script without any errors, e.g.

open FILE1,$file1 or die "$!";
my @file1=<FILE1>;
close FILE1;

What perl environment are you using? Strawberry? ActiveState? Cygwin?

Annihilannic.

 

[perl145]

yes it is reading the file without any errors.
I am using ActiveState..

 

[Annihilannic]

Unfortunately the only box I have access to with ActiveState perl on it doesn't have Excel installed.

Did you try chazoid's suggestion? What was the output?

Annihilannic.

 

[tsuji]

[0]
> my $file1 = <STDIN>;
> chomp($file1);

I had suggested that in a previous thread.

http://www.tek-tips.com/viewthread.cfm?qid=1608185

[tt]
use File::Spec;
chomp(my $file1=<STDIN>);
$file1=File::Spec->rel2abs($file1);
die "$file1 does not exist, operation aborted.\n" unless -e $file1;
[/tt]
[1] Without chomp(), the range still works, but it would be preferred with chomp() to eliminate all doubt.

>my $cs1 = <STDIN>;
[tt]chomp(my $cs1 = <STDIN>[/tt]
etc...