Win32:OLE UsedRange error 0x80020005: "Type mismatch"
Hi, I am using excel 2003 Active Perl 5.8.8 Build 817 and cannot get the UsedRange syntax (as Per Perl Monks tips example) to work. When I use Strict I fail with a run time compile error for xlprevious. When I don't use Strict I get a type mismatch error on SearchDirection. When I initially installed the OLE module I also got a version control issue and had to change the OLE.PM hard coded version from 1704 to 1403.
I can confirm that excel is installed as I can perform other excel functions ... it mainly seems related to the xlnnnnn type parameters. I previously had success with this code with different version of excel and active state perl 5.6.
Please Help.
This is the essence of my code
#!C:/Perl/bin/perl
use strict;
use warnings;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard);
use Win32::OLE qw(in valof with);
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...
my $DateFormat = "DDD mmm dd, yyyy -- hh:mm:ss";
# Directory to store uploaded customer files
my $uploadc_dir = "E:/Inetpub/wwwroot/someplace/somefilelib";
my $query = new CGI;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0;
my $IBook = $Excel->Workbooks->Open("E:\\afile");
# select worksheet number 1 (you can also select a worksheet by name) and activate it
my $ISheet = $IBook->Worksheets(1);
$ISheet->Activate();
# Find last row and last column
my $LastRow = $ISheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};
my $LastCol = $ISheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByColumns})->{Column};
Re: Win32:OLE UsedRange error 0x80020005: "Type mismatch"
created: 2006-04-03 09:27:17
I dunno. It all works just fine for me. (AS Perl, build 806)
In order to try to reproduce your error, I tried commenting out the use Win32::OLE::Const 'Microsoft Excel'; line. Indeed, that causes xlPrevious (etc.) to be undefined:
Bareword "xlPrevious" not allowed while "strict subs" in use...which is not a "type mismatch" error as you say you got.
Then I tried removing strict, and that allowed an OLE error to occur, but again, not the same as the one you say you got:
Unable to get the Find property of the Range class
But the discrepancies may perhaps be due simply to version differences. To eliminate one possible source of the error, I would look up the actual numeric values for those contants and use those.
Oh, here: I've done it for you.
xlPrevious = 2 xlByRows = 1 xlByColumns = 2
We're building the house of the future together.
Re^2: Win32:OLE UsedRange error 0x80020005: "Type mismatch"
created: 2006-04-04 07:55:26
You are absolutely correct ... when I put in the values rather than the constants it works to a "tee" as one might say. Thankyou ... Thankyou ... Thankyou a true provider of perl wisdom.