在运行VB脚本时禁用Excel中的所有对话框? [英] Disable all dialog boxes in Excel while running VB script?

问题描述

我在VB中有一些代码将所有XLSM文件保存为XLSX.我已经有了可以为我做的代码，但是每一个动作都会显示对话框.几十个文件就可以了.但是，我将一次在数百个XLSM文件上使用它，而我不能整日坐在我的计算机上，一遍又一遍地单击对话框.

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.

我尝试过的代码很多:

Application.DisplayAlerts = False

虽然这不会导致错误，但也无法正常工作.

While this doesn't cause an error, it also doesn't work.

这些框会发出有关启用宏的警告，并警告另存为XLSX会剥离所有宏的文件.考虑到警告的类型，由于安全风险，我怀疑它们已限制关闭这些对话框.

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.

由于我是在Excel的VB编辑器中运行此代码的，所以也许有一个选项可以让我禁用用于调试的对话框?

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?

我也尝试过:

Application.DisplayAlerts = False       
Application.EnableEvents = False        
' applied code
Application.DisableAlerts = True
Application.EnableEvents = True

这些都不起作用.

这是上面的代码在我当前代码中的样子:

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

相对于ActiveWorkbook.SaveAs行，我还包围了For循环:

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

最后，我将Application.DisplayAlerts移到了Workbooks.Open行上方:

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

这些都不起作用.

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

I'm using Excel for Mac 2011, if that helps.

推荐答案

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

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

如此:

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

这篇关于在运行VB脚本时禁用Excel中的所有对话框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！