Suggest a filename for SaveAs

[Jimbob]

Hello
I'm using Excel 2003 and want to suggest a filename to users from a cell
within a workbook. Although I want to suggest the name of the file, I want
them to be able to browse to ensure the file is located in their own choice
of folder.

The code I have is:
ActiveWorkbook.saveAs Filename:=("AD1").Value

This just saves the file in the same folder as its source.

Any clues please?

 

[JW]

Hello
I'm using Excel 2003 and want to suggest a filename to users from a cell
within a workbook. Although I want to suggest the name of the file, I want
them to be able to browse to ensure the file is located in their own choice
of folder.

The code I have is:
ActiveWorkbook.saveAs Filename:=("AD1").Value

This just saves the file in the same folder as its source.

Any clues please?

Application.GetSaveAsFilename Range("AD1").Value

 

[Ron de Bruin]

Hi Jimbob

You can use GetSaveAsFilename
Here is a tester that use SaveCopyAs

Sub Test1()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename(Sheets("Sheet1").Range("AD1").Value, _
fileFilter:="Excel Files (*.xls), *.xls")
If fname = False Then Exit Sub
If Dir(fname) <> "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub

 

[Jimbob]

Apologies that I haven't been able to re-visit this item until now.

Very many thanks for your help on this. It works, but . .

Ron, when I execute your solution, a copy of the workbook is saved with its
new name but the version on screen retains the original name.
Why should this be please, and how to prevent it so that the active workbook
is the saved one?

Thanks again

 

[Ron de Bruin]

Hi Jimbob

I use as a example SaveCopyAs

You can change it to SaveAs

 

[Jimbob]

Fantastic.

You guys are brilliant
Thank you

Hi Jimbob

I use as a example SaveCopyAs

You can change it to SaveAs