如何在不关闭调用工作簿的情况下使用 VBA SaveAs? [英] How to use VBA SaveAs without closing calling workbook?

问题描述

我想:

• 使用模板工作簿进行数据操作
• 将此工作簿的副本另存为 .xlsx(SaveCopyAs让你改变文件类型，否则这会很棒)
• 继续显示原始模板(不是另存为"模板)

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

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

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

不幸的是，这意味着:

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

有没有办法做到这一点?

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

另请注意，虽然 SaveCopyAs 将让我将其另存为不同类型(即 templateWb.SaveCopyAs FileName:="myXlsx.xlsx")，但在打开时会出现错误因为它现在的文件格式无效.

Also note while SaveCopyAs will 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.

推荐答案

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

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.

Sub saveExample()
    Application.ScreenUpdating = False

    mySaveCopyAs ThisWorkbook, "C:Temp	estfile2", 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

这篇关于如何在不关闭调用工作簿的情况下使用 VBA SaveAs?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！