我在下面的代码中将第一张工作表以外的所有工作表从工作簿A(宏文件)复制到工作簿B(普通xlsx)。我能够成功执行此过程。但是,当用户在“保存”对话框窗口中单击“取消”时,我需要处理一种情况。我想向用户弹出一条消息,指出“您单击了取消,因此该文件尚未保存。请重试”,并且不应打开或保存其他工作簿。
现在,当我单击“取消”时,将保存一个名为TRUE.xlsx的工作簿。
Dim NewWkb As Workbook
Dim xWkb As Workbook
Dim x As Integer
Dim varResult As Variant
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Set xWkb = ThisWorkbook
Set NewWkb = Workbooks.Add
For x = 2 To xWkb.Worksheets.count
xWkb.Worksheets(x).copy after:=NewWkb.Worksheets(NewWkb.Worksheets.count)
Next x
NewWkb.Worksheets("Sheet1").Delete
NewWkb.Worksheets(1).Activate
NewWkb.SaveAs varResult = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xlsx), *.xls")
End Sub
You need to first get the filename into your variable
varResult
and then use that to specify the destination for.SaveAs
:另外,我建议检查用户是否已按下“取消”按钮。