VBA Script needed to Change default 'SAVE' location but NOT save file automatically

[hallred]

I have a file that is distributed to many users. I need to embed a VBA script in the file that when run opens the 'Save As' dialogue box to a specific folder location BUT does not save the file.

The users will need to select a sub-folder within the primary folder and enter the file name in order to complete the save function. I see many codes to save the file automatically but I need the users to actually complete this process manually.

Please help.

 

[yky]

http://www.mrexcel.com/forum/excel-...ave-dialog-box-visual-basic-applications.html

 

[hallred]

yky,

Thanks for the link. I didn't find that when I did my original search. I've got this code working exactly as I need it except the file location does not default to where the users need to be. I need the save as location to start in "//corporate/certificates"

Sub FileSave()
Dim IntialName As String
Dim fileSaveName As Variant
InitialName = Range("K5")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
    Filefilter:="Excel Files (*.xlsm), *.xls")


If fileSaveName <> False Then
    MsgBox "Save as " & fileSaveName
End If
End Sub

 

[yky]

Try this:

Sub FileSave()
Dim IntialName As String
Dim fileSaveName As Variant
[COLOR=#0000cd]Const Path_Prefix="//corporate/certificates/"[/COLOR]
InitialName = Range("K5")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
    Filefilter:="Excel Files (*.xlsm), *.xls")

If fileSaveName <> False Then
   [COLOR=#0000cd] fileSaveName= Path_Prefix & fileSaveName[/COLOR]
    MsgBox "Save as " & fileSaveName
End If
End Sub