为什么 VBA ActiveWorkbook.SaveAs 会更改打开的电子表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5483725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does VBA ActiveWorkbook.SaveAs change the open spreadsheet?
提问by deltanovember
My function is as follows:
我的功能如下：
Sub saveCSV()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"c:\temp\file.csv", FileFormat:=xlCSV _
, CreateBackup:=False
End Sub
I'm trying to export the active worksheet to CSV. When I run the code in the title, Book1.xlsm changes to file.csv and Sheet1 changes to file. The export works fine. How can I do the export without these unwanted side effects?
我正在尝试将活动工作表导出为 CSV。当我运行标题中的代码时，Book1.xlsm 更改为 file.csv，Sheet1 更改为文件。导出工作正常。如何在没有这些不需要的副作用的情况下进行导出？
回答by Joel Goodwin
That's always how SaveAs has worked. The only way to get around this is to copy the worksheet and do a SaveAs on the copy, then close it.
这就是 SaveAs 的工作方式。解决这个问题的唯一方法是复制工作表并在副本上执行另存为，然后关闭它。
EDIT:I should add an example as it's not that difficult to do. Here's a quick example that copies the ActiveSheetto a new workbook.
编辑：我应该添加一个例子，因为这并不难。这是一个将 复制ActiveSheet到新工作簿的快速示例。
Dim wbk As Workbook
Set wbk = Workbooks.Add
ActiveSheet.Copy wbk.Sheets(1) ' Copy activesheet before the first sheet of wbk
wbk.SaveAs ....
wbk.Close
A complicated workbook may get issues with links and macros, but in ordinary scenarios this is safe.
复杂的工作簿可能会出现链接和宏的问题，但在普通情况下这是安全的。
EDIT 2:I'm aware of what you're trying to do, as your other question was about trying to trigger an export on every change to the sheet. This copy sheet approach presented here is likely to be highly disruptive.
编辑 2：我知道您要做什么，因为您的另一个问题是关于尝试在对工作表的每次更改时触发导出。此处介绍的这种复印表方法可能具有高度破坏性。
My suggestion is to write a CSV file by hand to minimise GUI interruption. The sheet is likely to become unusable if the saves are occurring at high frequency. I wouldn't lay the blame for this at the door of Excel, it simply wasn't built for rapid saves done behind the scenes.
我的建议是手工编写一个 CSV 文件以尽量减少 GUI 中断。如果保存频率很高，则该工作表可能无法使用。我不会把这归咎于 Excel，它根本不是为了在幕后完成的快速保存而构建的。
回答by tpascale
Here's a little routine that does what you want by operating on a copy of the original ... copy made via file scripting object. Hardcoded to operate on "ThisWorkbook" as opposed to active workbook & presumes ".xlsm" suffix - could tweak this to do the job I think:
这是一个小程序，它通过操作原始副本的副本来完成您想要的操作……通过文件脚本对象制作的副本。硬编码以操作“ThisWorkbook”而不是活动工作簿并假定“.xlsm”后缀 - 可以调整它以完成我认为的工作：
Public Sub SaveCopyAsCsv()
Dim sThisFile As String: sThisFile = ThisWorkbook.FullName
Dim sCsvFile As String: sTempFile = Replace(sThisFile, ".xlsm", "_TEMP.xlsm")
ThisWorkbook.Save ' save the current workbook
' copy the saved workbook ABC.xlsm to TEMP_ABC.xlsm
Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Call fso.deletefile(sTempFile, True) ' deletes prev temp file if it exists
On Error GoTo 0
Call fso.CopyFile(sThisFile, sTempFile, True)
' open the temp file & save as CSV
Dim wbTemp As Workbook
Set wbTemp = Workbooks.Open(sTempFile) ' open the temp file in excel
' your prev code to save as CSV
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="c:\temp\file.csv", FileFormat:=xlCSV, CreateBackup:=False
wbTemp.Close ' close the temp file now that the copy has been made
Application.DisplayAlerts = True
' delete the temp file (if you want)
On Error Resume Next
Call fso.deletefile(sTempFile, True) ' deletes the temp file
On Error GoTo 0
End Sub
