How to backup Excel using VBA?

[skarkada]

I am looking for a "Save As" type functionality without changing the
Excel's current file.

What I am trying to do is to save a snapshot of the current worksheet
in CSV format before my macro's logic begins to execute.
ActiveWorkbook.SaveAs method worked exactly like I wanted, but it
changed the current file in the Excel.

SaveCopyAs method would work fine, but it insists on saving the whole
workbook and also doesn't support changing the format to CSV.

I could read the contents of the worksheet, convert to CSV strings,
open a text file, and write to it. But, if there is a built-in
function, I don't want to reinvent the wheel.

Thank you for your time.

 

[Peter T]

see SaveCopyAs in help.

Regards,
Peter T

 

[skarkada]

see SaveCopyAs in help.

Regards,
Peter T

Peter, thanks for your message. SaveCopyAs wouldn't save as CSV and
wouldn't let me save just the current worksheet. We are on Excel 2003
version.

 

[Peter T]

Peter, thanks for your message. SaveCopyAs wouldn't save as CSV
and wouldn't let me save just the current worksheet.

Sorry, I didn't read your post carefully before replying.
How about this -

Sub test()
Dim ws As Worksheet

Set ws = ActiveSheet ' change to suit
ws.Copy

Application.DisplayAlerts = False
' code to ensure filename does not already exist
ActiveWorkbook.SaveAs Filename:="C:\CVS_test.csv", FileFormat:=xlCSV
ActiveWorkbook.Close

Application.DisplayAlerts = true
End Sub


Regards,
Peter T

 

[skarkada]

Wonderful solution!

Thank you very much for your help.