Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Win::OLE Excel: disable macros

by Ratazong (Monsignor)
on Feb 16, 2022 at 09:49 UTC ( #11141413=perlquestion: print w/replies, xml ) Need Help??

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

Dear monks,

I'm using Win::OLE for working with Excel ... and normally all runs fine.

But now I have to open an .xlsm-file with a corrupted macro that runs when opening the file. (When opening the file manually I get an error message I need to confirm; if I globally disable macros in Excel the file can be opened without errors)

Is there a way to open that file with macros disabled (using Win::OLE)? Somehow I cannot find it ... :-(

Thanks in advance, Rata

Replies are listed 'Best First'.
Re: Win::OLE Excel: disable macros
by haukex (Archbishop) on Feb 16, 2022 at 10:07 UTC

    The documentation of Workbooks.Open says:

    By default, macros are enabled when opening files programmatically. Use the AutomationSecurity property to set the macro security mode used when opening files programmatically.

    This works for me:

    use warnings; use strict; use Win32::OLE (); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # die on errors my $Excel = Win32::OLE->new('Excel.Application', 'Quit') or die "Faile +d to open Excel"; # or to get running Excel instance: Win32::OLE->GetActiveObject('Excel +.Application') my $const = Win32::OLE::Const->Load('Microsoft Office '.$Excel->{Versi +on}.' Object Library'); $Excel->{AutomationSecurity} = $const->{msoAutomationSecurityForceDisa +ble}; $Excel->{Visible} = 1; my $Book = $Excel->Workbooks->Open("filename.xlsm"); $Book->Activate; # ... $Excel->Quit;

      Thank you haukex and Anonymous Monk!

      msoAutomationSecurityForceDisable does the trick :-) .

      note: I found out that it works with $Excel->Workbooks->Open, but it didn't work with $Excel->Workbooks->OpenXML


Re: Win::OLE Excel: disable macros
by Anonymous Monk on Feb 16, 2022 at 10:22 UTC

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11141413]
Approved by choroba
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2023-04-02 09:29 GMT
Find Nodes?
    Voting Booth?

    No recent polls found