Disable all dialog boxes in Excel while running VB

问题:

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 <> ""
        If Right(WorkFile, 4) <> "xlsx" Then
            Workbooks.Open Filename:=myPath & 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 <> ""
        If Right(WorkFile, 4) <> "xlsx" Then
            Workbooks.Open Filename:=myPath & 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.

回答1:

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 <> ""
        If Right(WorkFile, 4) <> "xlsx" Then
            Workbooks.Open Filename:=myPath & 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

回答2:

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.

回答3:

From Excel Macro Security - www.excelfunctions.net:

Macro Security in Excel 2007, 2010 & 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.

回答4:

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
}

回答5:

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