Win32:OLE UsedRange error 0x80020005: "Type mismatch"

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

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};
[download]

Edited 2 Apr 2006 (~21:30 PDT) by footpad: Added rudimentary formatting tags.

Comment on Win32:OLE UsedRange error 0x80020005: "Type mismatch" Download Code

Replies are listed 'Best First'.
Re: Win32:OLE UsedRange error 0x80020005: "Type mismatch" by jdporter (Paladin) on Apr 03, 2006 at 13:27 UTC
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...` [download] 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` [download] 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` [download] We're building the house of the future together.	[reply] [d/l] [select]
Re^2: Win32:OLE UsedRange error 0x80020005: "Type mismatch" by Anonymous Monk on Apr 04, 2006 at 11:55 UTC
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.	[reply]