Alter macro security level programmatically

[Dale Fye]

I have an Access application that is opening a number of Excel spreadsheets
and reading data from them. Each of these spreadsheets has the same macros,
which I want to disable when I open the workbook, rather than having to click
the pop-up window that asks if I want to enable/disable macros.

Is there a way to do this using Excel automation? How about using API
calls? My intent is that I would like to read the current setting, change
the setting to disable macros, read in the files, then reset the setting to
its original value.

 

[Don Guillett]

Think about it. If you could then what is the use of having security in the
first place?

 

[Gary''s Student]

You may be able to adapt this to Access:

Sub is_it_safe()
Dim s As String
Dim s2 As String
s2 = "C:\temp\child1.xls /s"
s = "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.exe "
x = Shell(s & s2, 1)
End Sub

The idea is to open child1.xls in Excel in Office Safe Mode. This should
disable any child1 startup macro activity.

 

[Jon Peltier]

You could use alternative approaches to read the data, for example:

How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/kb/257819

How is Access opening the Excel workbooks? If you use VBA to open a document
with macros in an instance of its parent application, the fact that VBA is
running counts as an approval to run code, so you should not be warned.

- Jon

 

[Dale Fye]

Great idea Jon. Had not even considered using ADO to read data from Excel.
I'll take a look.

My problem stems from Access misinterpreting data types when importing or
linking to Excel, so opening an ADO recordset might just do the trick.

Thanks.
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

I get your point, but my code is digitally signed, so it can run at the
higher security levels.

My preference was to Open the workbook, hoping that there was a way within
the GetObject or wbk.Open methods to disable the macros automatically. In
the absense of that, I was looking for any other possible alternatives, Jon's
idea to open an ADO recordset may just work.

Thanks for your feedback.
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.