我在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