I need some help saving files during a macro:

1) I want to specify a path & file name for the active file to be saved
with - no user intervention required. The following code works for the file
name but location seems random - how do I specify where the file should be
saved?

ActiveWorkbook.SaveAs Filename:="my new file.xls"

2) I want to end my macro by having the user save the active workbook with
location and file name of their choice. I have used the following code to
display the SaveAs dialog but when the user clicks Save, the file is not
actually saved. What extra code is needed to execute the Save using location
& file name from the SaveAs dialog?

Application.FileDialog(msoFileDialogSaveAs).Show

Lynn

1) You need to specify the path. For example if you want the file to alway
save to the C drive in a folder called MyWorkbooks then your code should be
as follows:

ActiveWorkbook.SaveAs Filename:="C:\MyWorkbooks\my new file.xls"

2) That line of code only gets the filepath/name. It does not save the
file. Use the following:

FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
If FileSaveName = False Then End
ActiveWorkbook.SaveAs Filename:=FileSaveName


Mike

"Lynn" wrote:

I need some help saving files during a macro:

1) I want to specify a path & file name for the active file to be saved
with - no user intervention required. The following code works for the file
name but location seems random - how do I specify where the file should be
saved?

ActiveWorkbook.SaveAs Filename:="my new file.xls"

2) I want to end my macro by having the user save the active workbook with
location and file name of their choice. I have used the following code to
display the SaveAs dialog but when the user clicks Save, the file is not
actually saved. What extra code is needed to execute the Save using location
& file name from the SaveAs dialog?

Application.FileDialog(msoFileDialogSaveAs).Show

Thanks SO much!!

"crazybass2" wrote:

Lynn

1) You need to specify the path. For example if you want the file to alway
save to the C drive in a folder called MyWorkbooks then your code should be
as follows:

ActiveWorkbook.SaveAs Filename:="C:\MyWorkbooks\my new file.xls"

2) That line of code only gets the filepath/name. It does not save the
file. Use the following:

FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
If FileSaveName = False Then End
ActiveWorkbook.SaveAs Filename:=FileSaveName


Mike

"Lynn" wrote:

I need some help saving files during a macro:

1) I want to specify a path & file name for the active file to be saved
with - no user intervention required. The following code works for the file
name but location seems random - how do I specify where the file should be
saved?

ActiveWorkbook.SaveAs Filename:="my new file.xls"

2) I want to end my macro by having the user save the active workbook with
location and file name of their choice. I have used the following code to
display the SaveAs dialog but when the user clicks Save, the file is not
actually saved. What extra code is needed to execute the Save using location
& file name from the SaveAs dialog?

Application.FileDialog(msoFileDialogSaveAs).Show