Help with Saving a file with an invalid path

[UMAKEMESIK]

I am trying to save a file to a path on my company's network. When I type in this line in my - run box in windows it recognizes it and takes me to the proper folder.

Now I am trying to save to that same folder with a specified file name.

excel does not like this line and can not find the path.

"\\fp_slz\users\%username%\sr.xls" _

Is there something in the above line that does not work in excel, does it not like some of the characters?

Below is the full saving command.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
            "\\fp_slz\users\%username%\sr.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

Any help would be appreciated.

 

[jmthompson]

I think you'd be fine with one tweak:

Application.DisplayAlerts = False
ThisWorkbook.SaveAs ("\\fp_slz\users\%username%\sr.xls")
Application.DisplayAlerts = True

 

[Norie]

I don't think it's going to recognise %username%, which as far as I know is how you would include an environment variable like username.

When you do type this in Windows what's the actual name of the folder it takes you to?

The %username% should have been replaced with an actual user name.

You can use Environ("username") to get the username in VBA and then incorporate that in the path.

Something like this perhaps:

strUserName = Environ("username")
 
strPath = \\fp_slz\users\" & strUserName% & "\sr.xls"

 

[UMAKEMESIK]

Thanks for all the help.

Here is what I was able to get to work:

With Sheets(1)
        strUserName = Environ("username")
End With

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
            strPath = "\\fpslz\users\" & strUserName & "\sr.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

I took out the %. They seemed to be giving me errors. Everything test out fine on my first couple of test.

THANKS AGAIN!!!!!

 

[UMAKEMESIK]

OOPS, SPOKE TO SOON.

The code just flys by this like it's not even there

With Sheets(1)
        strUserName = Environ("username")
End With

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
            strPath = "\\fpslz\users\" & strUserName & "\sr.xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

Any formatting help would be appreciated.

 

[shg]

When I type in this line in my - run box in windows it recognizes it and takes me to the proper folder.

What shows in the address bar when the folder opens?

 

[xenou]

@UMake You have this in your last post:

ActiveWorkbook.SaveAs Filename:= _
           [COLOR="Red"] strPath = "\\fpslz\users\" & strUserName & "\sr.xls"[/COLOR] _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

That's not exactly what Norie provided as an example.
It should be this:

strUserName = Environ("username")
strPath = "\\fpslz\users\" & strUserName & "\sr.xls"
ActiveWorkbook.SaveAs Filename:= strPath, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False