Setting path when saving excel workbook

[Sketchin]

HI All,

I am trying to write some code to open an existing excel form, populate it, and save it with a custom filename in a directory that is built using variables I have declared.

The only part I am having difficulty with is setting the pathname. Here is my code:

    'Build path to save file to
    spath = "S:\Contracts\Geomatics LAB\Signed\" & Company

  'Build File name
  Filename = "" & reservationNumberforForm & " " & Company & "-Payment Schedule"
     
  'Save the payment schedule to the S: drive
    'xlApp.ActiveWorkbook.SaveAs Filename:="S:\Contracts\Geomatics LAB\Signed\" & Filename, FileFormat:=xlOpenXMLWorkbook
    xlApp.ActiveWorkbook.SaveAs Filename:=[COLOR="Red"]"" & spath & " [/COLOR]" & Filename, FileFormat:=xlOpenXMLWorkbook
    xlApp.ActiveWorkbook.Saved = True

The part in red is where the real problem is.

Thanks for the help

 

[AlexHedley]

Do you have a
Dim spath As String

If you do
Debug.Print spath
before the Save what gets printed in the console?

 

[Sketchin]

Yes I do. Its not that I'm getting an error, its that the file is being saved like this:

S:\Contracts\Geomatics LAB\Signed\University of Alberta RES-004 University of Alberta-Payment Schedule.xlsx

After the first University of Alberta, there should be a \

 

[Sketchin]

i think i figured it out by adding "\" to the end of this:

spath = "S:\Contracts\Geomatics LAB\Signed\" & Company

Now I am getting run-time error 1004!!

 

[Sketchin]

Run-time error is due to the directory not being created.

Thanks for the feedback!

 

[AlexHedley]

Glad it's working, I've missed the slash myself a number of times!

You might want to use Dir() to check it exists and MkDir() if it doesn't.