Saving A File

[Himszy]

Hi,

Anyway to save a file using a macro?

Thanks Michael

 

[Nick Hodge]

Himszy

I have used the ThisWorkbook object which refers to the workbook containing
the code, but you could use ActiveWorkbook, Workbooks("NameOfBook"),
Workbooks(1)

ThisWorkbook.Save

ThisWorkbook.SaveAs FileName:="xxxxxx.xls"


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Himszy]

Thanks Nick
How do you change the path of the file?
Thanks Michael

 

[Nick Hodge]

Michael

Saving the file will save it to either the default location (Under
tools>options>general) if it's a new book or to the location where the
workbook resides for existing files. If you want to save it elsewhere you
are going to need 'save as' instead of 'save'.

There are tons of ways but if you want to offer a prompt for the user then
use

Sub saveFile()
Dim sFileName As String
sFileName = Application.GetSaveAsFilename()
ThisWorkbook.SaveAs Filename:=sFileName
End Sub

The getsaveasfilename simply presents the save as box and returns the name
and path of the file, you then use that to 'save as' the file.

You could use the hardcode of the file path if it doesn't change, like this.

ThisWorkbook.SaveAs Filename:="C:\" & sFilename

Or you could use the ChDir Statement to change the 'current directory'
ChDir("C:\")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)