Disable all dialog boxes in Excel while running VB script

Question

I have some code in VB that saves all XLSM files as XLSX  I already have the code that will do that for me  but dialog boxes show up for every action  This was fine for a few dozen files  However  I m going to use this on hundreds of XLSM files at once  and I can t just sit at my computer all day clicking dialog boxes over and over    The code I ve tried is pretty much   Application DisplayAlerts   False   While this doesn t cause an error  it also doesn t work    The boxes give a warning about enabling macros  and also warn that saving as XLSX strips the file of all macros  Considering the type of warnings  I suspect that they ve restricted turning off those dialog boxes due to the security risk    Since I m running this code in Excel s VB editor  is there perhaps an option that will allow me to disable dialog boxes for debugging   I ve also tried   Application DisplayAlerts   False        Application EnableEvents   False           applied code Application DisableAlerts   True Application EnableEvents   True   Neither of those worked    Edit   Here s what the code above looks like in my current code   Public Sub example   Application DisplayAlerts   False Application EnableEvents   False  For Each element In sArray     XLSMToXLSX element  Next element  Application DisplayAlerts   False Application EnableEvents   False End Sub  Sub XLSMToXLSX ByVal file As String      Do While WorkFile  lt  gt             If Right WorkFile  4   lt  gt   xlsx  Then             Workbooks Open Filename  myPath  amp  WorkFile              Application DisplayAlerts   False             Application EnableEvents   False              ActiveWorkbook SaveAs Filename                 modifiedFileName  FileFormat                 xlOpenXMLWorkbook  CreateBackup  False              Application DisplayAlerts   True             Application EnableEvents   True              ActiveWorkbook Close         End If         WorkFile   Dir       Loop End Sub   I also surrounded the For loop  as opposed to the ActiveWorkbook SaveAs line   Public Sub example   For Each element In sArray     XLSMToXLSX element  Next element End Sub   Finally  I shifted the Application DisplayAlerts above the Workbooks Open line   Sub XLSMToXLSX ByVal file As String      Do While WorkFile  lt  gt             If Right WorkFile  4   lt  gt   xlsx  Then             Workbooks Open Filename  myPath  amp  WorkFile              Application DisplayAlerts   False             Application EnableEvents   False              ActiveWorkbook SaveAs Filename                 modifiedFileName  FileFormat                 xlOpenXMLWorkbook  CreateBackup  False              Application DisplayAlerts   True             Application EnableEvents   True              ActiveWorkbook Close         End If         WorkFile   Dir       Loop End Sub   None of those work as well   Edit   I m using Excel for Mac 2011  if that helps

User · Answer

From Excel Macro Security - www excelfunctions net      Macro Security in Excel 2007  2010  amp  2013                  The different Excel file types provided by the latest versions of   Excel make it clear when workbook contains macros  so this in itself   is a useful security measure  However  Excel also has optional macro   security settings  which are controlled via the options menu  These   are          Disable all macros without notification           This setting does not allow any macros to run   When you open a new   Excel workbook  you are not alerted to the fact that it contains   macros  so you may not be aware that this is the reason a workbook   does not work as expected            Disable all macros with notification           This setting prevents macros from running  However  if there are   macros in a workbook  a pop-up is displayed  to warn you that the   macros exist and have been disabled            Disable all macros except digitally signed macros           This setting only allow macros from trusted sources to run  All other   macros do not run   When you open a new Excel workbook  you are not   alerted to the fact that it contains macros  so you may not be aware   that this is the reason a workbook does not work as expected            Enable all macros           This setting allows all macros to run   When you open a new Excel   workbook  you are not alerted to the fact that it contains macros and   may not be aware of macros running while you have the file open       If you trust the macros and are ok with enabling them  select this option       Enable all macros     and this dialog box should not show up for macros   As for the dialog for saving  after noting that this was running on Excel for Mac 2011  I came across the following question on SO  StackOverflow - Suppress dialog when using VBA to save a macro containing Excel file   xlsm  as a non macro containing file   xlsx   From it  removing the dialog does not seem to be possible  except for possibly by some Keyboard Input simulation  I would post another question to inquire about that  Sorry I could only get you halfway  The other option would be to use a Windows computer with Microsoft Excel  though I m not sure if that is a option for you in this case

User · Answer

In order to get around the Enable Macro prompt I suggest  Application AutomationSecurity   msoAutomationSecurityForceDisable   Be sure to return it to default when you are done  Application AutomationSecurity   msoAutomationSecurityLow   A reminder that the  SaveAs function contains all optional arguments I recommend removing CreatBackup   False as it is not necessary   The most interesting way I think is to create an object of the workbook and access the  SaveAs property that way   I have not tested it but you are never using Workbooks Open rendering Application AutomationSecurity inapplicable  Possibly saving resources and time as well   That said I was able to execute the following without any notifications on Excel 2013 windows 10       Option Explicit      Sub Convert        OptimizeVBA  True         function to set all the things you want to set  but hate keying in      Application AutomationSecurity   msoAutomationSecurityForceDisable        this should stop those pesky enable prompts      ChDir  F  VBA Macros Stack Overflow Questions When changing type xlsm to      xlsx stop popup       Workbooks Open   Book1 xlsm        ActiveWorkbook SaveAs Filename          F  VBA Macros Stack Overflow Questions When changing type xlsm to xlsx       stop popup Book1 xlsx          FileFormat  xlOpenXMLWorkbook      ActiveWorkbook Close      Application AutomationSecurity   msoAutomationSecurityLow       make sure you set this up when done      Kill   F  VBA Macros Stack Overflow Questions When changing type xlsm       to xlsx stop popup Book1 xlsx    clean up      OptimizeVBA  False      End Sub       Function OptimizeVBA ByRef Status As Boolean       If Status   True Then         Application ScreenUpdating   False         Application Calculation   xlCalculationManual         Application DisplayAlerts   False         Application EnableEvents   False     Else         Application ScreenUpdating   True         Application Calculation   xlCalculationAutomatic         Application DisplayAlerts   True         Application EnableEvents   True     End If      End Function

User · Answer

Have you tried using the ConflictResolution  xlLocalSessionChanges parameter in the SaveAs method   As so   Public Sub example   Application DisplayAlerts   False Application EnableEvents   False  For Each element In sArray     XLSMToXLSX element  Next element  Application DisplayAlerts   False Application EnableEvents   False End Sub  Sub XLSMToXLSX ByVal file As String      Do While WorkFile  lt  gt             If Right WorkFile  4   lt  gt   xlsx  Then             Workbooks Open Filename  myPath  amp  WorkFile              Application DisplayAlerts   False             Application EnableEvents   False              ActiveWorkbook SaveAs Filename                 modifiedFileName  FileFormat                 xlOpenXMLWorkbook  CreateBackup  False                ConflictResolution  xlLocalSessionChanges              Application DisplayAlerts   True             Application EnableEvents   True              ActiveWorkbook Close         End If         WorkFile   Dir       Loop End Sub

User · Answer

Solution  Automation Macros  It sounds like you would benefit from using an automation utility   If you were using a windows PC I would recommend AutoHotkey   I haven t used automation utilities on a Mac  but this Ask Different post has several suggestions  though none appear to be free   This is not a VBA solution   These macros run outside of Excel and can interact with programs using keyboard strokes  mouse movements and clicks   Basically you record or write a simple automation macro that waits for the Excel  Save As  dialogue box to become active  hits enter return to complete the save action and then waits for the  Save As  window to close   You can set it to run in a continuous loop until you manually end the macro   Here s a simple version of a Windows AutoHotkey script that would accomplish what you are attempting to do on a Mac   It should give you an idea of the logic involved   Example Automation Macro  AutoHotkey      Infinite loop   End the macro by closing the program from the Windows taskbar  Loop            Wait for ANY  Save As  dialogue box in any program          BE CAREFUL           Ignore the  Confirm Save As  dialogue if attempt is made          to overwrite an existing file      WinWait  Save As    Confirm Save As     IfWinNotActive  Save As    Confirm Save As         WinActivate  Save As    Confirm Save As     WinWaitActive  Save As    Confirm Save As      sleep  250     0 25 second delay     Send   ENTER      Save the Excel file           Wait for the  Save As  dialogue box to close      WinWaitClose  Save As    Confirm Save As

User · Answer

In Access VBA I ve used this to turn off all the dialogs when running a bunch of updates   DoCmd SetWarnings False   After running all the updates  the last step in my VBA script is   DoCmd SetWarnings True   Hope this helps

[vba] Disable all dialog boxes in Excel while running VB script?

Examples related to vba

Examples related to excel

Examples related to excel-2011