将工作簿保存在用户指定的路径中

我在下面的代码中将第一张工作表以外的所有工作表从工作簿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
评论
  • ket
    ket 回复

    You need to first get the filename into your variable varResult and then use that to specify the destination for .SaveAs:

    Dim varResult As Variant
    
    c= Application.GetSaveAsFilename(fileFilter:="Text Files (*.xlsx), *.xls")
    
    'check if user has pressed cancel
    If VarType(varResult) = vbBoolean And varResult = False Then
        Exit Sub
    End If
    
    NewWkb.SaveAs varResult
    

    另外,我建议检查用户是否已按下“取消”按钮。