File save as with path statement

Posted by Stan Mitterling on April 03, 2001 8:37 AM

I have a master spreadsheet that pulls costs from two other spreadsheets. These three sheets are read-only so that the users cannot write over them when they make changes. I have written a macro to save the file with a specific name based on what the user's have entered in. My question is I want to specify the path that the file is saved to. The path needs to be based on the user's home directory. I can bring the user's login name into the spreadsheet. I just can't get the path statement to work.
Server is NT 4.0 with the client using Win98. Excel 2000.

Posted by Dave Hawley on April 03, 2001 8:44 AM

Hi Stan

This code will pass the default file path of the users machine:

Sub TryThis()
Dim FilePath As String

FilePath = Application.DefaultFilePath
MsgBox FilePath

End Sub

Dave
OzGrid Business Applications

Posted by Stan Mitterling on April 03, 2001 10:38 AM

I saw that in the VB online help. Only problem with that is it can be changed by the user. We are going to using NTFS permissions to stop users from getting into the other people's directories. If user changes it he could lock himself out form his own directory. The home directory is going to be set by a login script from the NT server. I would like to be able to set the path with a generic path at the beginning (I.E. t:\sales\quotes\xxxxx) With xxxx being the users login name. I can pull the login name into Excel and I just can't figure out to set a path name using the xxxx variable and the variable that I have setup for the file name.
Thanks for your previous reply.

Posted by Dave Hawley on April 03, 2001 10:55 AM

You mean like:

Application.DefaultFilePath = "t:\sales\quotes\" & xxxxx

Dave

OzGrid Business Applications

Posted by Stan Mitterling on April 03, 2001 12:16 PM

I tried that and it works, only problem I believe is if you save the spreadsheet somewhere else and then use the macro to save the file it will default to where you last saved the file, not the default path. I would like to force the path every time they use the macro to save the file in a specific location.
Thanks for your help

Posted by Dave Hawley on April 03, 2001 12:24 PM

Try placing:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Me.SaveAs "t:\sales\quotes\" & xxxxx
Cancel = True
End Sub

In the module of "ThisworkBook"

Dave

OzGrid Business Applications