CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Advanced:
Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing?

 



popx
New User


Mar 11, 2010, 12:37 AM

Post #1 of 2 (2666 views)
Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing? Can't Post

I'm using Win32::OLE to control Excel, and want to run Excel macros from Perl such that errors, for example the VBA dialog box that pops up after a divide-by-zero, don't make my Perl program hang while waiting for a human to press one of the buttons on the dialog box. I ask because I am using Perl to run Excel on a Web server, executing spreadsheets uploaded by customers. I can't know their spreadsheets' content in advance. So I can't edit their VBA code to insert "On Error" traps. Instead, I need Perl to trap _all_ VBA errors, tell me whether an error occurred, and prevent Excel hanging after the error. From the Win32::OLE documentation, I couldn't find out how to do this. (Although - as I describe at the end - I half have, as long as I hold open an extra Excel instance.) Can anybody suggest a way?

I've searched all "Win32" postings on this forum, and found nothing apposite. However, I have done some experiments, and I'll describe their results below, showing my test program. There may be a connection with a related problem as described in the Xtreme Visual Basic Talk forum at http://www.xtremevbtalk.com/showthread.php?t=296563 . Here, a reply by Microsoft MVP Mike Rosenblum ( at http://www.xtremevbtalk.com /showpost.php?p=1287607&postcount=2 ) says that the VBA code is run in a separate thread, and that therefore, error trapping will not work because the caller (Perl, for me) cannot trap the error thrown on the callee (the VBA macro)'s thread.

First, here is my test program.


Code
  use strict; 
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

# Start, and open spreadsheet.
#
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open("C:\\test\\test15.xls");

# Switch off warnings and pop-ups, I hope.
#
$Win32::OLE::Warn = 0;
$Excel->{DisplayAlerts}=0;

# Let me see the spreadsheet.
#
$Excel->{Visible}=1;

# Am about to put a value (the time) into A1, then
# call a macro that has a divide-by-0 error.
# The macro updates cell A1 first. After.
# calling the macro, see whether A1 has changed.
# Also display the last OLE error.
#
my $time = localtime;
print "Am about to put \"" . $time . "\" into A1.\n";
$Excel->Sheets("Sheet1")->Range("A1")->{Value} = $time;
my $A1 = $Excel->Sheets("Sheet1")->Range("A1")->{Text};
print "A1 before calling first macro = \"" . $A1 . "\".\n";
print "Am about to call macro.\n";
$Excel->Run("ProvokeDivError1");
my $error = Win32::OLE->LastError();
print "Error = " . $error . ".\n";
$A1 = $Excel->Sheets("Sheet1")->Range("A1")->{Text};
print "A1 after calling macro = \"" . $A1 . "\".\n\n";

print "After first macro call and before second one.\n\n";

# Now the same for the second macro, but
# with A2.
#
my $time = localtime;
print "Am about to put \"" . $time . "\" into A2.\n";
$Excel->Sheets("Sheet1")->Range("A2")->{Value} = $time;
my $A2 = $Excel->Sheets("Sheet1")->Range("A2")->{Text};
print "A2 before calling second macro = \"" . $A2 . "\".\n";
print "Am about to call macro.\n";
$Excel->Run("ProvokeDivError2");
$error = Win32::OLE->LastError();
print "Error = " . $error . ".\n";
$A2 = $Excel->Sheets("Sheet1")->Range("A2")->{Text};
print "A2 after calling macro = \"" . $A2 . "\".\n\n";

print "After second macro call.\n\n";

# Demonstrate that spreadsheet is still
# working, and usable for calculation.
#
$time = localtime;
print "Am about to put \"" . $time . "\" into A3.\n";
$Excel->Sheets("Sheet1")->Range("A3")->{Value} = $time;
my $A3 = $Excel->Sheets("Sheet1")->Range("A3")->{Text};
print "A3 = \"" . $A3 . "\".\n";
my $A4 = $Excel->Sheets("Sheet1")->Range("A4")->{Text};
print "A4 is defined to be this appended to itself:\n";
print "A4 = \"" . $A4 . "\".\n";


Second, I need to describe my spreadsheet: the C:\test\test15.xls opened by the script. It contains the default three sheets Sheet1, Sheet2, and Sheet3, of which I use only Sheet1. All cells are blank, except for A4, which is defined (cf. the final lines above) as

Code
  =A3&A3

The spreadsheet has one VBA module that defines two macros. Each macro sets a cell, then provokes a divide-by-zero error, as follows:


Code
  ' This subroutine provokes a divide-by-zero error, 
' but only after setting Sheet1!A1 to 1.
'
Public Sub ProvokeDivError1()
Worksheets("Sheet1").Range("A1") = 1
Debug.Print 1 / 0
End Sub

' This subroutine provokes a divide-by-zero error,
' but only after setting Sheet1!A2 to 2.
'
Public Sub ProvokeDivError2()
Worksheets("Sheet1").Range("A2") = 2
Debug.Print 1 / 0
End Sub


Third, here is a link to my script and spreadsheet, in case anyone wants to download and try them. They're in http://www.j-paine.org/excel_from_perl.zip .

Fourth, when I run the script, I see Excel open, and then I get the standard VBA dialog box saying "Run-time error '11'", "Division by zero", with the buttons End, Debug, and Help. If $Win32::OLE::Warn is 3, the script exits after I press End on the first dialog box.

However, if $Win32::OLE::Warn is 0, 1, or 2, the script continues when I press End, then when I press End on the next dialog box. Its output shows that the macros have changed cells A1 and A2 before crashing, and that after the second macro call, A4 correctly calculates a result based on A3. (To recap, what I want Perl to do is to continue right through the script in this way, but without the dialog box popping up.)

This is the output from the script. The two (**)'s are where I press End on the dialog boxes:


Code
  C:\test>perl test3.pl 
Am about to put "Thu Mar 11 08:13:07 2010" into A1.
A1 before calling first macro = "Thu Mar 11 08:13:07 2010".
Am about to call macro. (**)
Error = Win32::OLE(0.1709) error 0x800a9c68
in METHOD/PROPERTYGET "Run".
A1 after calling macro = "1".

After first macro call and before second one.

Am about to put "Thu Mar 11 08:13:59 2010" into A2.
A2 before calling second macro = "Thu Mar 11 08:13:59 2010".
Am about to call macro. (**)
Error = Win32::OLE(0.1709) error 0x800a9c68
in METHOD/PROPERTYGET "Run".
A2 after calling macro = "2".

After second macro call.

Am about to put "Thu Mar 11 08:15:15 2010" into A3.
A3 = "Thu Mar 11 08:15:15 2010".
A4 is defined to be this appended to itself:
A4 = "Thu Mar 11 08:15:15 2010Thu Mar 11 08:15:15 2010".

C:\test>


Fifth, I found by accident that if I hold one instance of Excel open while running my script, the script does continue to the end. However, the macros do not update the cells that they should. This is very odd.

To demonstrate this, I open two DOS windows and in both, change to the directory where my script is. In the script, I set $Win32::OLE::Warn to 0. From one DOS window, I run the script. I let it run until the first dialog box appears. I then do nothing more with it.

Then from the second DOS window, I also run the script. It ran all the way, and I could see the cells in the previous Excel instance being updated. However, the macros did not update A1 and A2. This is the script's output:


Code
  C:\test>perl test3.pl 
Am about to put "Thu Mar 11 07:51:04 2010" into A1.
A1 before calling first macro = "Thu Mar 11 07:51:04 2010".
Am about to call macro.
Error = Win32::OLE(0.1709) error 0x800a9c68
in METHOD/PROPERTYGET "Run".
A1 after calling macro = "Thu Mar 11 07:51:04 2010".

After first macro call and before second one.

Am about to put "Thu Mar 11 07:51:04 2010" into A2.
A2 before calling second macro = "Thu Mar 11 07:51:04 2010".
Am about to call macro.
Error = Win32::OLE(0.1709) error 0x800a9c68
in METHOD/PROPERTYGET "Run".
A2 after calling macro = "Thu Mar 11 07:51:04 2010".

After second macro call.

Am about to put "Thu Mar 11 07:51:04 2010" into A3.
A3 = "Thu Mar 11 07:51:04 2010".
A4 is defined to be this appended to itself:
A4 = "Thu Mar 11 07:51:04 2010Thu Mar 11 07:51:04 2010".

C:\test>


Any ideas on this would be very welcome.

Cheers,

Jocelyn Paine
http://www.j-paine.org
http://www.spreadsheet-parts.org


popx
New User


Apr 7, 2010, 1:13 AM

Post #2 of 2 (2547 views)
Re: [popx] Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing? [In reply to] Can't Post

I got some very helpful info about this from Microsoft MVP Mike Rosenblum. It's in the How to Catch An Excel Runtime Error From a WinForm? thread at Xtreme VB Talk, and our discussion begins at posting #7. For details, see the postings. Mike concludes that VBA is implemented in a way that makes it impossible to trap errors in a program written exactly like the Perl script I showed; but one can do it if one calls the macros from another Excel instance, and calls that from outside. In posting #12 I show a Java program that demonstrates this.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives