I am using VBA to create a loop to open up a number of XL files - however I
want disable any macros that the files may contain when opening these files

Any help gratefully received

Thanks

James

Try this: it will do the job
place this code in the main file then open the other files, so the
codes in the other files will not be excuted.

the code will open the other files and prevent excuting the codes
inside them


Code:
--------------------
Sub Security()
Dim lngAutomation As MsoAutomationSecurity

With Application
lngAutomation = .AutomationSecurity
.AutomationSecurity = msoAutomationSecurityForceDisable
With .FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
.AutomationSecurity = lngAutomation
End With

End Sub

--------------------


--
helmekki


------------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384353

James

If this is because you are trying to get around the security warning, AFAIK
there is no way.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"
wrote in message ...
I am using VBA to create a loop to open up a number of XL files - however I
want disable any macros that the files may contain when opening these
files

Any help gratefully received

Thanks

James

Helmekki

That doesn't seem to stop the warning. It had gone past me totally and
reading help appears to state it will work, but I'd be staggered if you can
actually switch off warnings so simply on something so crucial as macro
security or am I misunderstanding

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"helmekki" wrote in
message ...

Try this: it will do the job
place this code in the main file then open the other files, so the
codes in the other files will not be excuted.

the code will open the other files and prevent excuting the codes
inside them


Code:
--------------------
Sub Security()
Dim lngAutomation As MsoAutomationSecurity

With Application
lngAutomation = .AutomationSecurity
.AutomationSecurity = msoAutomationSecurityForceDisable
With .FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
.AutomationSecurity = lngAutomation
End With

End Sub

--------------------


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384353

Nick,
After reading:
http://msdn.microsoft.com/library/de...us/vbaxl10/htm
l/xlproAutomationSecurity.asp

I am confused as well.
Does this mean I can dictate Excel2002 macro security from code,
irrespective of the user's setting ?

As I do not have XL2002 I cannot test.

NickHK


"Nick Hodge" wrote in message
...
Helmekki

That doesn't seem to stop the warning. It had gone past me totally and
reading help appears to state it will work, but I'd be staggered if you
can
actually switch off warnings so simply on something so crucial as macro
security or am I misunderstanding

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"helmekki" wrote
in
message ...

Try this: it will do the job
place this code in the main file then open the other files, so the
codes in the other files will not be excuted.

the code will open the other files and prevent excuting the codes
inside them


Code:
--------------------
Sub Security()
Dim lngAutomation As MsoAutomationSecurity

With Application
lngAutomation = .AutomationSecurity
.AutomationSecurity = msoAutomationSecurityForceDisable
With .FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
.AutomationSecurity = lngAutomation
End With

End Sub

--------------------


--
helmekki


------------------------------------------------------------------------
helmekki's Profile:
http://www.excelforum.com/member.php...fo&userid=6939
View this thread:
http://www.excelforum.com/showthread...hreadid=384353

What I want to do is to audit files. However I don't want to start any
Auto_Open macros when I open a file. I have listed below a cut down version
of the macro I am using. When I open up the first file the macro just stops.
Any thoughts?


Sub AuditFiles()
Dim iFilename As Object
Dim lngAutomation As MsoAutomationSecurity

lngAutomation = Application.AutomationSecurity

For Each iFilename In Range("FilesToAudit")
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open FileName:=iFilename, ReadOnly:=True, password:="",
updatelinks:=0
'test workbook

Application.AutomationSecurity = lngAutomation
Workbooks(iFilename).Close savechanges:=False
Next iFilename
End Sub

"helmekki" wrote:


Try this: it will do the job
place this code in the main file then open the other files, so the
codes in the other files will not be excuted.

the code will open the other files and prevent excuting the codes
inside them


Code:
--------------------
Sub Security()
Dim lngAutomation As MsoAutomationSecurity

With Application
lngAutomation = .AutomationSecurity
.AutomationSecurity = msoAutomationSecurityForceDisable
With .FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
.AutomationSecurity = lngAutomation
End With

End Sub

--------------------


--
helmekki


------------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384353

I managed to get round the problem by creating a new instance of Excel to
open the files and disabling the macros there

Thanks for pointing me in the right direction


" wrote:

What I want to do is to audit files. However I don't want to start any
Auto_Open macros when I open a file. I have listed below a cut down version
of the macro I am using. When I open up the first file the macro just stops.
Any thoughts?


Sub AuditFiles()
Dim iFilename As Object
Dim lngAutomation As MsoAutomationSecurity

lngAutomation = Application.AutomationSecurity

For Each iFilename In Range("FilesToAudit")
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open FileName:=iFilename, ReadOnly:=True, password:="",
updatelinks:=0
'test workbook

Application.AutomationSecurity = lngAutomation
Workbooks(iFilename).Close savechanges:=False
Next iFilename
End Sub

"helmekki" wrote:


Try this: it will do the job
place this code in the main file then open the other files, so the
codes in the other files will not be excuted.

the code will open the other files and prevent excuting the codes
inside them


Code:
--------------------
Sub Security()
Dim lngAutomation As MsoAutomationSecurity

With Application
lngAutomation = .AutomationSecurity
.AutomationSecurity = msoAutomationSecurityForceDisable
With .FileDialog(msoFileDialogOpen)
.Show
.Execute
End With
.AutomationSecurity = lngAutomation
End With

End Sub

--------------------


--
helmekki


------------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=384353

NIckH, james.

tested in Excel 2002 SP3, Win2K SP4. Macro Security = medium.

BTW James, which excel version are u using?


With Application
.AutomationSecurity = msoAutomationSecurityForceDisable
.Workbooks.Open "D:\Personal Data\book1.xls"
End With

Does not show a security promt, while there is a code module in the
workbook.

PS the bevaior if this settings changed slightly in excel 2003
(http://www.kbalertz.com/kb_825939.aspx)

DM Unseen

Win 2K SP4, Excel 2003

However, I wanted to run this in Excel 97 (...I know...long story), but it
appears that 97 doesn't have automation security

James


"DM Unseen" wrote:

NIckH, james.

tested in Excel 2002 SP3, Win2K SP4. Macro Security = medium.

BTW James, which excel version are u using?


With Application
.AutomationSecurity = msoAutomationSecurityForceDisable
.Workbooks.Open "D:\Personal Data\book1.xls"
End With

Does not show a security promt, while there is a code module in the
workbook.

PS the bevaior if this settings changed slightly in excel 2003
(http://www.kbalertz.com/kb_825939.aspx)

DM Unseen

James,
I have the same problem. I want to disable macros and events (running
on the file that is opened with VBA) when opening a file with VBA.
Since I use Excel 2000 I cannot use AutomationSecurity property. That
is why I'm curious about how You did get around this problem.

I tried to use
Application.EnableEvents = False
, but it does not work. Events runs anyway.

Please send a code example if it's possible.

Thanks

/Patrik


--
patrikj
------------------------------------------------------------------------
patrikj's Profile: http://www.excelforum.com/member.php...o&userid=29604
View this thread: http://www.excelforum.com/showthread...hreadid=384353

Hi,

Have you tried holding down the 'shift' key while opening your workbook?

I'm not sure if it works on all versions of excel, but I use it from time to
time (same thing may work for Access unless it's disabled).

Cheers,
--
Kevin Lehrbass

www.spreadsheetsolutions4u.com


"patrikj" wrote:


James,
I have the same problem. I want to disable macros and events (running
on the file that is opened with VBA) when opening a file with VBA.
Since I use Excel 2000 I cannot use AutomationSecurity property. That
is why I'm curious about how You did get around this problem.

I tried to use
Application.EnableEvents = False
, but it does not work. Events runs anyway.

Please send a code example if it's possible.

Thanks

/Patrik


--
patrikj
------------------------------------------------------------------------
patrikj's Profile: http://www.excelforum.com/member.php...o&userid=29604
View this thread: http://www.excelforum.com/showthread...hreadid=384353