如何在不关闭调用工作簿的情况下使用 VBA SaveAs？

How to use VBA SaveAs without closing calling workbook?

提问by enderland

I want to:

我想要：

• Do data manipulation using a Template workbook
• Save a copy of this work book as .xlsx (SaveCopyAsdoesn't let you change filetypes, otherwise this would be great)
• Continue showing original template (not the "saved as" one)

• 使用模板工作簿进行数据操作
• 将此工作簿的副本另存为 .xlsx（SaveCopyAs不允许您更改文件类型，否则会很棒）
• 继续显示原始模板（不是“另存为”模板）

Using SaveAsdoes exactly what is expected - it saves the workbook while removing the macros and presents me the view of the newly created SavedAs workbook.

UsingSaveAs完全符合预期 - 它在删除宏的同时保存工作簿并向我显示新创建的 SavedAs 工作簿的视图。

This unfortunately means:

不幸的是，这意味着：

• I no longer am viewing my macro enabled workbook unless I reopen it
• Code execution stops at this point because
• Any macro changes are discarded if I forget to save (note: for a production environment this is ok, but, for development, it's a huge pain)

• 我不再查看启用宏的工作簿，除非我重新打开它
• 代码执行在此时停止，因为
• 如果我忘记保存，任何宏更改都会被丢弃（注意：对于生产环境，这是可以的，但是，对于开发来说，这是一个巨大的痛苦）

Is there a way I can do this?

有没有办法做到这一点？

'current code
Application.DisplayAlerts = False
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
templateWb.Activate
Application.DisplayAlerts = True

'I don't really want to make something like this work (this fails, anyways)
Dim myTempStr As String
myTempStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name
ThisWorkbook.Save
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Workbooks.Open (myTempStr)

'I want to do something like:
templateWb.SaveCopyAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 'SaveCopyAs only takes one argument, that being FileName

Also note while SaveCopyAswill let me save it as a different type (ie templateWb.SaveCopyAs FileName:="myXlsx.xlsx") this gives an error when opening it because it now has an invalid file format.

另请注意，whileSaveCopyAs会让我将其另存为不同类型（即templateWb.SaveCopyAs FileName:="myXlsx.xlsx"），这在打开它时会出现错误，因为它现在具有无效的文件格式。

采纳答案by enderland

I did something similar to what Siddharth suggested and wrote a function to do it as well as handle some of the annoyances and offer some more flexibility.

我做了一些类似于 Siddharth 建议的事情，并编写了一个函数来做到这一点，同时处理一些烦恼并提供更多的灵活性。

Sub saveExample()
    Application.ScreenUpdating = False

    mySaveCopyAs ThisWorkbook, "C:\Temp\testfile2", xlOpenXMLWorkbook

    Application.ScreenUpdating = True
End Sub

Private Function mySaveCopyAs(pWorkbookToBeSaved As Workbook, pNewFileName As String, pFileFormat As XlFileFormat) As Boolean

    'returns false on errors
    On Error GoTo errHandler



     If pFileFormat = xlOpenXMLWorkbookMacroEnabled Then
        'no macros can be saved on this
        mySaveCopyAs = False
        Exit Function
    End If

    'create new workbook
    Dim mSaveWorkbook As Workbook
    Set mSaveWorkbook = Workbooks.Add

    Dim initialSheets As Integer
    initialSheets = mSaveWorkbook.Sheets.Count


    'note: sheet names will be 'Sheet1 (2)' in copy otherwise if
    'they are not renamed
    Dim sheetNames() As String
    Dim activeSheetIndex As Integer
    activeSheetIndex = pWorkbookToBeSaved.ActiveSheet.Index

    Dim i As Integer
    'copy each sheet
    For i = 1 To pWorkbookToBeSaved.Sheets.Count
        pWorkbookToBeSaved.Sheets(i).Copy After:=mSaveWorkbook.Sheets(mSaveWorkbook.Sheets.Count)
        ReDim Preserve sheetNames(1 To i) As String
        sheetNames(i) = pWorkbookToBeSaved.Sheets(i).Name
    Next i

    'clear sheets from new workbook
    Application.DisplayAlerts = False
    For i = 1 To initialSheets
        mSaveWorkbook.Sheets(1).Delete
    Next i

    'rename stuff
    For i = 1 To UBound(sheetNames)
        mSaveWorkbook.Sheets(i).Name = sheetNames(i)
    Next i

    'reset view
    mSaveWorkbook.Sheets(activeSheetIndex).Activate

    'save and close
    mSaveWorkbook.SaveAs FileName:=pNewFileName, FileFormat:=pFileFormat, CreateBackup:=False
    mSaveWorkbook.Close
    mySaveCopyAs = True

    Application.DisplayAlerts = True
    Exit Function

errHandler:
    'whatever else you want to do with error handling
    mySaveCopyAs = False
    Exit Function


End Function

回答by Siddharth Rout

Here is a much faster method than using .SaveCopyAsto create a copy an then open that copy and do a save as...

这是一种比使用.SaveCopyAs创建副本然后打开该副本并另存为...

As mentioned in my comments, this process takes approx 1 second to create an xlsx copy from a workbook which has 10 worksheets (Each with 100 rows * 20 Cols of data)

正如我在评论中提到的，这个过程需要大约 1 秒才能从一个工作簿创建一个 xlsx 副本，该工作簿有 10 个工作表（每个工作表有 100 行 * 20 列数据）

Sub Sample()
    Dim thisWb As Workbook, wbTemp As Workbook
    Dim ws As Worksheet

    On Error GoTo Whoa

    Application.DisplayAlerts = False

    Set thisWb = ThisWorkbook
    Set wbTemp = Workbooks.Add

    On Error Resume Next
    For Each ws In wbTemp.Worksheets
        ws.Delete
    Next
    On Error GoTo 0

    For Each ws In thisWb.Sheets
        ws.Copy After:=wbTemp.Sheets(1)
    Next

    wbTemp.Sheets(1).Delete
    wbTemp.SaveAs "C:\Blah Blah.xlsx", 51

LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

回答by AndASM

There is nothing pretty or nice about this process in Excel VBA, but something like the below. This code doesn't handle errors very well, is ugly, but should work.

这个过程在 Excel VBA 中没有什么好或好的，但类似于下面的内容。这段代码不能很好地处理错误，很丑，但应该可以工作。

We copy the workbook, open and resave the copy, then delete the copy. The temporary copy is stored in your local temp directory, and deleted from there as well.

我们复制工作簿，打开并重新保存副本，然后删除副本。临时副本存储在您的本地临时目录中，并从那里删除。

Option Explicit

Private Declare Function GetTempPath Lib "kernel32" _
         Alias "GetTempPathA" (ByVal nBufferLength As Long, _
         ByVal lpBuffer As String) As Long

Public Sub SaveCopyAs(TargetBook As Workbook, Filename, FileFormat, CreateBackup)
  Dim sTempPath As String * 512
  Dim lPathLength As Long
  Dim sFileName As String
  Dim TempBook As Workbook
  Dim bOldDisplayAlerts As Boolean
  bOldDisplayAlerts = Application.DisplayAlerts
  Application.DisplayAlerts = False

  lPathLength = GetTempPath(512, sTempPath)
  sFileName = Left$(sTempPath, lPathLength) & "tempDelete_" & TargetBook.Name

  TargetBook.SaveCopyAs sFileName

  Set TempBook = Application.Workbooks.Open(sFileName)
  TempBook.SaveAs Filename, FileFormat, CreateBackup:=CreateBackup
  TempBook.Close False

  Kill sFileName
  Application.DisplayAlerts = bOldDisplayAlerts
End Sub

回答by arbitel

I have a similar process, here's the solution I use. It allows the user to open a template, perform manipulation, save the template somewhere, and then have the original template open

我有一个类似的过程，这是我使用的解决方案。它允许用户打开模板，执行操作，将模板保存在某处，然后打开原始模板

1. user opens macro-enabled template file
2. do manipulation
3. save ActiveWorkbook's file path (template file)
4. execute a SaveAs
5. set ActiveWorkbook (now the saveas'd file) as a variable
6. open template file path in step 3
7. close the variable in step 5

1. 用户打开启用宏的模板文件
2. 做操纵
3. 保存 ActiveWorkbook 的文件路径（模板文件）
4. 执行另存为
5. 将 ActiveWorkbook（现在是另存为的文件）设置为变量
6. 在步骤 3 中打开模板文件路径
7. 关闭步骤 5 中的变量

the code looks something like this:

代码如下所示：

    'stores file path of activeworkbook BEFORE the SaveAs is executed
    getExprterFilePath = Application.ActiveWorkbook.FullName

    'executes a SaveAs
    ActiveWorkbook.SaveAs Filename:=filepathHere, _
    FileFormat:=51, _
    Password:="", _
    WriteResPassword:="", _
    ReadOnlyRecommended:=False, _
    CreateBackup:=False

    'reenables alerts
    Application.DisplayAlerts = True


    'announces completion to user
    MsgBox "Export Complete", vbOKOnly, "List Exporter"             


    'sets open file (newly created file) as variable
    Set wbBLE = ActiveWorkbook

    'opens original template file
    Workbooks.Open (getExprterFilePath)

    'turns screen updating, calculation, and events back on
    With Excel.Application
        .ScreenUpdating = True
        .Calculation = Excel.xlAutomatic
        .EnableEvents = True
    End With

    'closes saved export file
    wbBLE.Close

回答by OSKM

Another option (only tested on latest versions of excel).

另一种选择（仅在最新版本的 excel 上测试）。

The Macros are not deleted until the workbook is closed after a SaveAs.xlsxso you can do two SaveAsin quick succession without closing the workbook.

宏在工作簿关闭之前不会被删除，SaveAs.xlsx因此您可以SaveAs在不关闭工作簿的情况下快速连续执行两个操作。

ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True

Note: you need to turn off the DisplayAlertsto avoid getting the warning that the workbook already exists on the second save.

注意：您需要关闭DisplayAlerts以避免在第二次保存时收到工作簿已存在的警告。