MATLAB Central Newsreader - Disable Excel Macro

Disable Excel Macro

Alex Thiele — Tue, 06 Jan 2009 09:54:02 -0500

Hi all,

I have a script which interogates hundreds of .XLS files using XLSREAD. However, these XLS files all have macros which display a pop up window if macros are enabled.

This means that I have to sit at the computer closing all the pop up windows as they appear, rather than just being able to set the program off and walk away.

Does anyone know of a way in which I can disable these macros from my MATLAB script, or close the pop up windows as they appear?

Many thanks,

Alex

Re: Disable Excel Macro

Aslak Grinsted — Tue, 06 Jan 2009 10:14:02 -0500

"Alex Thiele" <bert1234@yahoo.com> wrote in message <gjv9nq$1v8$1@fred.mathworks.com>...
> Hi all,
>
> I have a script which interogates hundreds of .XLS files using XLSREAD. However, these XLS files all have macros which display a pop up window if macros are enabled.
>
> This means that I have to sit at the computer closing all the pop up windows as they appear, rather than just being able to set the program off and walk away.
>
> Does anyone know of a way in which I can disable these macros from my MATLAB script, or close the pop up windows as they appear?
>
> Many thanks,
>
> Alex

can you use 'basic' mode: (quote from help xlsread)

    [NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE,'basic') reads an XLS file as
    above, using basic input mode. This is the mode used on UNIX platforms
    as well as on Windows when Excel is not available as a COM server.
    In this mode, XLSREAD does not use Excel as a COM server, which limits
    import ability. Without Excel as a COM server, RANGE will be ignored
    and, consequently, the whole active range of a sheet will be imported.
    Also, in basic mode, SHEET is case-sensitive and must be a string.

Re: Disable Excel Macro

Alex Thiele — Tue, 06 Jan 2009 10:51:02 -0500

Hi,

Thank you for your reply.

Unfortunately I get the error message:

Warning: XLSREAD has limited import functionality on non-Windows platforms
or in basic mode. Refer to HELP XLSREAD for more information.
> In xlsread at 200
??? File contains unexpected record length. Try saving as Excel 98

Thanks again,

Alex

Re: Disable Excel Macro

ImageAnalyst — Tue, 06 Jan 2009 11:36:16 -0500

Warnings are not errors. You can probably ignore it. The important
things is, do you get the file created that you expected?

Re: Disable Excel Macro

Alex Thiele — Tue, 06 Jan 2009 11:56:02 -0500

Hi,

No I don't.
Actually the full error message is

> In xlsread at 200
  In SpreadsheetAnalyser>pushbutton1_Callback at 147
  In gui_mainfcn at 75
  In SpreadsheetAnalyser at 48
??? File contains unexpected record length. Try saving as Excel 98.

Error in ==> SpreadsheetAnalyser>pushbutton1_Callback at 147
                [WholeDataFile,txt] = xlsread([Pathname,Filename{1,counter}],'ATP results','A1:U146', 'basic'); %import data and delete unwanted rows and columns

Error in ==> gui_mainfcn at 75
        feval(varargin{:});

Error in ==> SpreadsheetAnalyser at 48
    gui_mainfcn(gui_State, varargin{:});

??? Error while evaluating uicontrol Callback.

I didn't post this bacuase I didn't think it contained useful information. But I could be wrong. If I remove the 'A1:U146', 'basic' from my XLS read statement, then it works, but with the macro problem, Adding the 'A1:U146', 'basic' gives me the error above.

Thanks again

Alex

Re: Disable Excel Macro

Andrew — Tue, 06 Jan 2009 13:50:18 -0500

On Jan 6, 4:56=A0am, "Alex Thiele" <bert1...@yahoo.com> wrote:
> Hi,
>
> No I don't.
> Actually the full error message is
>
> > In xlsread at 200
>
> =A0 In SpreadsheetAnalyser>pushbutton1_Callback at 147
> =A0 In gui_mainfcn at 75
> =A0 In SpreadsheetAnalyser at 48
> ??? File contains unexpected record length. =A0Try saving as Excel 98.
>
> Error in =3D=3D> SpreadsheetAnalyser>pushbutton1_Callback at 147
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 [WholeDataFile,txt] =3D xlsread([Pathname=
,Filename{1,counter}],'ATP results','A1:U146', 'basic'); %import data and d=
elete unwanted rows and columns
>
> Error in =3D=3D> gui_mainfcn at 75
> =A0 =A0 =A0 =A0 feval(varargin{:});
>
> Error in =3D=3D> SpreadsheetAnalyser at 48
> =A0 =A0 gui_mainfcn(gui_State, varargin{:});
>
> ??? Error while evaluating uicontrol Callback.
>
> I didn't post this bacuase I didn't think it contained useful information=
. But I could be wrong. If I remove the 'A1:U146', 'basic' from my XLS read=
statement, then it works, but with the macro problem, Adding the 'A1:U146'=
, 'basic' gives me the error above.
>
> Thanks again
>
> Alex

I don't know how to deal with this through Matlab, but I doubt that
you can over-ride macros with an external program like Matlab.
Obviously you have access to Excel. It would be fast and easy to
disable all macros in Excel. Just set the macro security level to
High, then restart Excel. Then you can run your program, and reset
the Excel macro security when you are done. Is this a possibility?

Andy

Re: Disable Excel Macro

Alex Thiele — Tue, 06 Jan 2009 15:30:05 -0500

Hi,

Thanks for the message. Unfortunately even if I set the macro security level to 'very high', these macros still run!

Regards,

Alex

Re: Disable Excel Macro

Aslak Grinsted — Wed, 07 Jan 2009 08:25:05 -0500

"Alex Thiele" <bert1234@yahoo.com> wrote in message <gjvtdt$rmv$1@fred.mathworks.com>...
> Hi,
>
> Thanks for the message. Unfortunately even if I set the macro security level to 'very high', these macros still run!
>
> Regards,
>
> Alex

Hi

I dont think it is possible then using the built-in xlsread function. But you can probably do it interfacing the activex component directly.

Try looking at this:
http://www.mathworks.com/matlabcentral/fileexchange/22365

You have to look through the documentation for the excel.application object to find out how you can load and disable macros.

http://msdn.microsoft.com/en-us/library/bb978780.aspx

Aslak

Re: Disable Excel Macro

Aslak Grinsted — Wed, 07 Jan 2009 08:48:03 -0500

Regarding disabling macros when reading an excel file.

> I dont think it is possible then using the built-in xlsread function. But you can probably do it interfacing the activex component directly.
>
> Try looking at this:
> http://www.mathworks.com/matlabcentral/fileexchange/22365
>
> You have to look through the documentation for the excel.application object to find out how you can load and disable macros.
>
> http://msdn.microsoft.com/en-us/library/bb978780.aspx
>
> Aslak

Here are some more clues.

http://www.eggheadcafe.com/software/aspnet/30310387/-deactivating-auto-mac.aspx

Re: Disable Excel Macro

Alex Thiele — Wed, 07 Jan 2009 11:29:02 -0500

Thank you for your message.

I tried making my own version of xlsread.

I tried lots of different variations of 'EnableEvents=False' before and after the open command (line 250 in xlsread)

Excel.Application.EnableEvents(false)
Excel.workbook.Application.EnableEvents(false)
Excel.EnableEvents(false)

etc, but the macro still appeared!

Thanks again,

Alex

Re: Disable Excel Macro

fhirsch@entrerock.com — Thu, 08 Jan 2009 15:58:32 -0500

Perhaps you could go check out the Application.automationSecurity
object in Excel 2007
This example is from VBA help in Excel 2007

This example captures the current automation security setting, changes
the setting to disable macros, displays the Open dialog box, and after
opening the selected document, sets the automation security back to
its original setting.

Microsoft Visual Basic for Applications
Sub Security()
    Dim secAutomation As MsoAutomationSecurity

    secAutomation = Application.AutomationSecurity

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Application.FileDialog(msoFileDialogOpen).Show

    Application.AutomationSecurity = secAutomation

End Sub