SaveAs Dialog box
 

I want to direct the user to save the file as a certain name, but the user
should to be able to browse the directory and choose where to save it.

For example, I want the user to have to save the file as "XYZ.xls". Can
someone provide the code that allows freedom on the save path, but not on the
file name?

Thanks in advance
Daniel Bonallack

SaveAs Dialog box
 

Daniel,

If you have xl2002 or xl2003 then you can use the FileDialog object to
have the user specify a folder path...
Application.FileDialog(msoFileDialogFolderPicker). Show

Otherwise, the following will display the suggested file name, but will not
prevent the user from changing the name.
'-----------------------------------------
Sub TestSaveAs()
Dim x As Variant
x = Application.Dialogs(xlDialogSaveAs).Show(arg1:="My File Name.xls")
If x = False Then
MsgBox "Cancel was clicked "
End If
End Sub
'--------------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Daniel Bonallack" wrote in message
...
I want to direct the user to save the file as a certain name, but the user
should to be able to browse the directory and choose where to save it.

For example, I want the user to have to save the file as "XYZ.xls". Can
someone provide the code that allows freedom on the save path, but not on the
file name?

Thanks in advance
Daniel Bonallack

SaveAs Dialog box
 

Hi Daniel

Another way:

Sub SaveFileCode()
Dim vF As Variant
Dim sFil As String
Dim ForcedName As String

ForcedName = "XYZ.xls"

vF = Application.GetSaveAsFilename(ForcedName, _
"Excel workbook (*.xls), *.xls")
If vF = False Then Exit Sub
sFil = CStr(vF)
If Right$(sFil, Len(ForcedName) + 1) < "\" & ForcedName Then _
sFil = Left$(sFil, InStrRev(sFil, "\")) & ForcedName

MsgBox "User chose " & sFil & _
". This code did not save the file"

If Dir(sFil) < "" Then MsgBox "btw " & sFil & _
" already exists. Ask to overwrite."

End Sub

HTH. Best wishes Harald

"Daniel Bonallack" skrev i
melding ...
I want to direct the user to save the file as a certain name, but the user
should to be able to browse the directory and choose where to save it.

For example, I want the user to have to save the file as "XYZ.xls". Can
someone provide the code that allows freedom on the save path, but not on
the
file name?

Thanks in advance
Daniel Bonallack