ActiveWorkbook.SaveAS and Using variables

[Jaydub]

I am Trying to use this comand to rename and relocate a file using a macro. If I do this it works.

ActiveWorkbook.SaveAs Filename:= _
"P:\PHX Station\Station Supervisors\Ramp Schedule\Weekly Ramp Schedule3-31-02 to 3-15-02.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


But I would like the location and file name to be in a variable I call strNewLoc. I can't get it to work at all. It tells me that ithe file is in use or doesn't exist. Run time error 1004. Below is the code that won't work. datEnd and datStart are dimed as date.

strNewLoc = "P:\PHX Station\Station Supervisors\Ramp Schedule\Ramp Schedule" & " " & datStart & "-" & datEnd & ".xls"

ActiveWorkbook.SaveAs Filename:= _
strNewLoc _
, FileFormat:=xlNormal, Password:="",WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[JustinEzequiel]

datEnd and datStart are dimed as date.

strNewLoc = "P:\PHX Station\Station Supervisors\Ramp Schedule\Ramp Schedule" & " " & datStart & "-" & datEnd & ".xls"

do a msgbox strNewLoc before the ActiveWorkbook.SaveAs call.
I am suspecting that there are unwanted slashes ("/&quot introduced when concatinating the unformatted dates.
you may want to format the dates first.
ex. Format$(datStart, "m-d-YY&quot

 

[MRussell]

The problem is the "\" in the file NAME! Excel does not allow that. Change the date variables to string variables and use a date format that uses "." instead of "\" and all should be well.

I just discovered InputBox and used it in my macro that used dates for file names (that is how I know what the problem is and it took a non-macro writer to figure it out.)
strR_Num = InputBox("What is the Rebate Number?&quot
Format for date with . instead of \ --
.NumberFormat = "mm.dd.yy"