Help with SaveAs path

[BGrubb]

I have an .xlsm file posted on Sharepoint to be accessed by a group of users some of which are not very proficient in Excel. I have a screen that opens when the file is opened that instructs them to save the file (form) on their PC before using it. Several have saved over it on the Sharepoint site or saved a copy on the sharepoint site since that is what comes up when they use "SaveAs". Is there something I can put in the code so it will come up to show their computer instead of the Sharepoint site to save it to. I tried ChDir to C:\ but that doesn't work because Sharepoint is on their C drive.

Any help would be appreciated.

 

[AlphaFrog]

Try using ChDrive instead...
ChDrive "C"


ChDir doesn't change the default drive letter. This is from the VBA help on ChDir...

Remarks

The ChDir statement changes the default directory but not the default drive. For example, if the default drive is C, the following statement changes the default directory on drive D, but C remains the default drive:
ChDir "D:\TMP"

 

[BGrubb]

Is there code that can direct them to "My Documents"?

 

[AlphaFrog]

    ChDrive "C"
    ChDir Environ$("USERPROFILE") & "\My Documents\"

 

[BGrubb]

It still brings me back to "pdspportal" where the original file is located. That is where I don't want them to save the file.

 

[AlphaFrog]

Show what you are doing. I'm not sure what "it" is.

 

[BGrubb]

I tried to do a print screen but it isn't working.
"It" is the Save As box that comes up.
The Save In: defaults to "Templates on pdspportal"
The original form is saved in the folder "Templates" on the "Commercial Sales Force Effectiveness" Sharepoint site. It looks like it is under "Web Folders"
That's where I'm getting hung up at. I know to click on the drop down arrow beside of Save in and change it to where I want to save the file but some of the users don't know that so I would like for the code to do that for them.
I hope this makes sense.

 

[AlphaFrog]

I guess I was hoping to see your code and not a picture.

This worked for me in changing the SaveAs dialog to default to the My Documents folder.

Sub SaveAs_My_Docs()
    
    Dim Filename As String
    
    ChDrive "C"
    ChDir Environ$("USERPROFILE") & "\My Documents\"
    
    Filename = Application.GetSaveAsFilename( _
               FileFilter:="xls Files (*.xls*), *.xls*")
    If Filename <> "False" Then ActiveWorkbook.SaveAs
    
End Sub

 

[xenou]

It would certainly be a benefit to this group of users if you simply teach them how to save a file. I wouldn't trust macros on this - assume your file isn't safe if its publicly available in a place where it can be copied over.

 

[BGrubb]

I'm trying to teach them but it is difficult. They are sales people and they are out in the field. Their expertise is selling, not computers, so I'm trying to make it as easy as possible for them.