lisa2moon, welcome to Perl Monks!

You are correct in believing that  unless (eval {...}) { #error handling } should catch the exception and add the worksheet if the worksheet you requested can't be found, but that only works if the API is coded/configured to throw exceptions.

Perl errors are only stored in $@ if the author of the API codes it that way. Sometimes packages will generate warnings rather than throw errors. Sometimes they will use the return value for an error. System level routines sometimes also set an error id variable: $! - see perlvar for further discussion.

As for your specific package, Win32::OLE, you can configure error handling (skim down the documentation page for the discussion of warn option (Win32::OLE->Option(Warn => 3);). I haven't used this module, but from what I can see in the documentation, if you want your code to die and throw an exception you can catch rather than disappear in the vapour of warnings, you'll need to set the warning level to 4 (croak). Alternatively, you can completely customize error handling by setting Warn to a code reference.

Update: added note about using code references to customize error handling.