为什么VBA ActiveWorkbook.SaveAs更改打开的电子表格？ [英] Why does VBA ActiveWorkbook.SaveAs change the open spreadsheet?

问题描述

我的功能如下：

  Sub saveCSV（）
 Application.DisplayAlerts = False 
 ActiveWorkbook.SaveAs文件名：= _ 
c：\temp\file.csv，FileFormat：= xlCSV _ 
，CreateBackup：= False 
 End Sub 
  

我正在尝试将活动工作表导出为CSV。当我在标题中运行代码时，Book1.xlsm将更改为file.csv并将Sheet1更改为文件。出口工作正常如何在没有这些不必要的副作用的情况下进行导出？

解决方案

解决这个问题的唯一方法是复制工作表，并在副本上执行SaveAs，然后关闭它。

编辑：添加一个例子，因为这不是很难做到。这是一个快速的例子，将 ActiveSheet 复制到新的工作簿。

  Dim wbk As Workbook 
设置wbk = Workbooks.Add 
 ActiveSheet.Copy wbk.Sheets（1）'在第一张wbk 
 wbk.SaveAs .... $ b $之前复制活动表b wbk.Close 
  

复杂的工作簿可能会遇到链接和宏的问题，但在普通情况下

编辑2：我知道您要做什么，因为您的另一个问题是尝试触发在每张纸上更改出口。我的建议是手工编写一个CSV文件，以最大限度地减少GUI的中断。如果保存是在高频率下发生的，该表可能会变得不可用。我不会把这个责任归咎于Excel的门户，它根本不是为了在幕后快速保存而设计的。

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?

解决方案

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.

EDIT: I should add an example as it's not that difficult to do. Here's a quick example that copies the ActiveSheet to a new workbook.

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.

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.

这篇关于为什么VBA ActiveWorkbook.SaveAs更改打开的电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！