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''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dale Fye" <(E-Mail Removed)> wrote in message
news:506EFD7E-EE98-4483-A04C-(E-Mail Removed)...
>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''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>

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.
--
Gary''s Student - gsnu200764


"Dale Fye" wrote:

> 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''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dale Fye" <(E-Mail Removed)> wrote in message
news:506EFD7E-EE98-4483-A04C-(E-Mail Removed)...
>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''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>

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.



"Jon Peltier" wrote:

> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Dale Fye" <(E-Mail Removed)> wrote in message
> news:506EFD7E-EE98-4483-A04C-(E-Mail Removed)...
> >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''t forget to rate the post if it was helpful!
> >
> > email address is invalid
> > Please reply to newsgroup only.
> >
>
>
>

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.



"Don Guillett" wrote:

> Think about it. If you could then what is the use of having security in the
> first place?
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dale Fye" <(E-Mail Removed)> wrote in message
> news:506EFD7E-EE98-4483-A04C-(E-Mail Removed)...
> >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''t forget to rate the post if it was helpful!
> >
> > email address is invalid
> > Please reply to newsgroup only.
> >
>
>