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

我尝试过的代码几乎是:

Application.DisplayAlerts = False


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

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

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

我也尝试过:

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


这些都不起作用。

编辑:

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

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


我还包围了For循环,而不是ActiveWorkbook.SaveAs行:

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


最后,我将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


这些都不起作用。

编辑:

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

最佳答案

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

因此:

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

09-30 13:24
查看更多