Why does VBA ActiveWorkbook.SaveAs change the open spreadsheet?

Question

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?

Answer 1

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.