How to change the default "SaveAs" dialog directory path without prompting user nor causing a "save"

[Ludwig]

I know I can change the SaveAs path using

application.GetSaveAsFilename initialfilename:="%userprofile%\Downloads"

but this results in the user being presented with the SaveAs dialog.

I want to change the SaveaS directory they are shown if/when they issue their own SaveAs (or Save) request.

Reason why:
This will only occur when the macros in the spreadsheet make the file ReadOnly (e.g. user has been inactive for a period of time - got that code working).
To then prevent the user from accidentally overwriting the *real* file or creating duplicates in that same network folder, I want to change the SaveAs location away from the current file's location path to one within their own profile but not actually issue any save command. It's unlikely they will want to save, and should be so, but I like to be as safe as I can and avoid an overwritten file, or a clutter of files where they shouldn't be.

Thanks for your help.

 

[fhqwgads]

you can use ChDir to change the current directory

hope this works

ChDir "C:\Users\" & Environ("username") & "\Downloads"

 

[Ludwig]

Thanks for the suggestion.

I have just tested it .. unfortunately although the command did not fail, it had no effect on the selected SaveAs folder shown in SaveAs.

 

[Fluff]

Try

ChDrive "C:\"
ChDir "C:\Users\" & Environ("username") & "\Downloads"

 

[Ludwig]

Try

ChDrive "C:\"
ChDir "C:\Users\" & Environ("username") & "\Downloads"

Thanks for suggesting also including the change drive command ... should have thought of that from a traditional batch cmd file perspective.

However, whilst commands don't fail there's still no change in the SaveAs dialog.

 

[Fluff]

This works for me

Sub t2()
ChDrive "C:\"
ChDir Environ("userprofile") & "\downloads\"
Application.GetSaveAsFilename
End Sub

 

[fhqwgads]

This works for me

does this work when just clicking file>save as>browse ?
running the code opens the downloads folder but just clicking save as always takes me to the open file location
using excel 2016

 

[RoryA]

Save as always defaults to the current location for a saved file, regardless of what the current directory is set to. You'd need a workbook_beforesave event to cancel the default dialog. Though if you don't want them to save the current workbook, perhaps you should either use a template or immediately save it as a temporary copy when opening it.