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
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
Please Login or Register to view this content.
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
[email protected]HIS
"[email protected]" <[email protected]>
wrote in message news:[email protected]...
>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
[email protected]HIS
"helmekki" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:#[email protected]...
> 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
> [email protected]HIS
>
>
> "helmekki" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > 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
"[email protected]" 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
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
[email protected]
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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks