vba 在运行 VB 脚本时禁用 Excel 中的所有对话框？

Disable all dialog boxes in Excel while running VB script?

提问by NJP

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.

我在 VB 中有一些代码可以将所有 XLSM 文件保存为 XLSX。我已经有了可以为我执行此操作的代码，但是每个操作都会显示对话框。这对于几十个文件来说很好。但是，我将一次在数百个 XLSM 文件上使用它，而且我不能整天坐在我的计算机上一遍又一遍地单击对话框。

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.

这些框给出关于启用宏的警告，并警告保存为 XLSX 会删除所有宏的文件。考虑到警告的类型，我怀疑由于安全风险，他们已经限制关闭这些对话框。

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?

由于我在 Excel 的 VB 编辑器中运行此代码，是否有选项可以让我禁用调试对话框？

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 Forloop, as opposed to the ActiveWorkbook.SaveAsline:

我还包围了For循环，而不是ActiveWorkbook.SaveAs线：

Public Sub example()
For Each element In sArray
    XLSMToXLSX(element)
Next element
End Sub

Finally, I shifted the Application.DisplayAlertsabove the Workbooks.Openline:

最后，我将Application.DisplayAlerts上面的内容移到了上面Workbooks.Open：

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.

如果有帮助，我正在使用 Excel for Mac 2011。

回答by LetEpsilonBeLessThanZero

Have you tried using the ConflictResolution:=xlLocalSessionChanges parameter in the SaveAs method?

您是否尝试过在 SaveAs 方法中使用 ConflictResolution:=xlLocalSessionChanges 参数？

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

回答by tysonwright

In Access VBA I've used this to turn off all the dialogs when running a bunch of updates:

在 Access VBA 中，我使用它在运行一堆更新时关闭所有对话框：

DoCmd.SetWarnings False

After running all the updates, the last step in my VBA script is:

运行所有更新后，我的 VBA 脚本中的最后一步是：

DoCmd.SetWarnings True

Hope this helps.

希望这可以帮助。

回答by jordanhill123

From Excel Macro Security - www.excelfunctions.net:

来自Excel 宏安全 - 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.

Excel 2007、2010 和 2013 中的宏安全性：

.....

最新版本的 Excel 提供的不同 Excel 文件类型清楚地表明工作簿何时包含宏，因此这本身就是一种有用的安全措施。但是，Excel 还具有可选的宏安全设置，这些设置通过选项菜单进行控制。这些是 ：

'禁用所有宏而不通知'

• 此设置不允许运行任何宏。当您打开一个新的 Excel 工作簿时，您不会注意到它包含宏这一事实，因此您可能没有意识到这是工作簿无法按预期工作的原因。

'通过通知禁用所有宏'

• 此设置可防止运行宏。但是，如果工作簿中有宏，则会显示一个弹出窗口，警告您宏存在并且已被禁用。

'禁用除数字签名宏以外的所有宏'

• 此设置仅允许来自受信任来源的宏运行。所有其他宏都不会运行。当您打开一个新的 Excel 工作簿时，您不会注意到它包含宏这一事实，因此您可能没有意识到这是工作簿无法按预期工作的原因。

'启用所有宏'

• 此设置允许运行所有宏。当您打开一个新的 Excel 工作簿时，您不会注意到它包含宏这一事实，并且可能不知道在打开文件时有宏在运行。

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.

至于保存对话框，在注意到这是在 Excel for Mac 2011 上运行后，我在 SO 上遇到了以下问题，StackOverflow - Suppress dialog when using VBA to save a macro contains Excel file (.xlsm) as a non macro包含文件 (.xlsx)。从它看来，删除对话框似乎是不可能的，除非可能通过一些键盘输入模拟。我会发布另一个问题来询问这个问题。对不起，我只能让你半途而废。另一种选择是使用带有 Microsoft Excel 的 Windows 计算机，但我不确定在这种情况下是否适合您。

回答by ChrisB

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 posthas several suggestions, though none appear to be free.

听起来您会从使用自动化实用程序中受益。如果您使用的是 Windows PC，我会推荐AutoHotkey。我没有在 Mac 上使用过自动化实用程序，但是这个 Ask Different 帖子有几个建议，尽管似乎没有一个是免费的。

This is not a VBA solution. These macros run outside of Excel and can interact with programs using keyboard strokes, mouse movements and clicks.

这不是 VBA 解决方案。这些宏在 Excel 之外运行，可以使用键盘敲击、鼠标移动和点击与程序进行交互。

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.

基本上，您记录或编写一个简单的自动化宏，等待 Excel 的“另存为”对话框变为活动状态，按 Enter/返回以完成保存操作，然后等待“另存为”窗口关闭。您可以将其设置为连续循环运行，直到您手动结束宏。

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.

这是 Windows AutoHotkey 脚本的一个简单版本，可以完成您在 Mac 上尝试执行的操作。它应该让您了解所涉及的逻辑。

Example Automation Macro: AutoHotkey

示例自动化宏：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
}

回答by Joshua Fenner

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 .SaveAsfunction contains all optional arguments.I recommend removing CreatBackup:= Falseas it is not necessary.

提醒该.SaveAs函数包含所有可选参数。我建议删除CreatBackup:= False它，因为它不是必需的。

The most interesting way I think is to create an object of the workbook and access the .SaveAsproperty that way. I have not tested it but you are never using Workbooks.Openrendering Application.AutomationSecurityinapplicable. Possibly saving resources and time as well.

我认为最有趣的方法是创建工作簿的对象并以.SaveAs这种方式访问属性。我没有测试过，但你从来没有使用过不适用的Workbooks.Open渲染Application.AutomationSecurity。也可能节省资源和时间。

That said I was able to execute the following without any notifications on Excel 2013 windows 10.

也就是说，我能够在 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