How to save a Excel in VBA?

Question

I have a Excel sheet, and if I save the file using the Save as... option in Excel VBA the currently open document would close, and switch over to the newly created document.

How can I save a copy of the document without switching over the control?

Answer 1

file_name = Application.GetSaveAsFilename(InitialFileName:="File Name", _
filefilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")

Worksheets("Sheet1").Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:=file_name
wb.Close

Answer 2

You can use SaveCopyAs to do what you want

ActiveWorkbook.SaveCopyAs "C:\XXXX.XLS"

Answer 3

CurrentFile = ThisWorkbook.FullName
ActiveWorkbook.SaveAs "C:\myfile.xls", FileFormat:=52
Workbooks.Open CurrentFile