Run-time error 1004 when trying to save a sheet as PDF

[willow1985]

This code works just fine in another spreadsheet I use. All I have to do is set what I want printed (set print area) and then run this code however with a new spreadsheet I have I am getting error 1004 and I am not sure why.

Here is the code, any idea what I could be missing?

The cell references for file name and file locations in Z1 and Z2 are formulas. Is there something I have to do to the code to make it understand/read the formula result value for the directory in Z1?

Sub SavePDF()
'
' SavePDF Macro
'
Dim Path As String
Dim filename As String

Path = Sheets("Defects by Workstation Table").Range("Z1")
filename = Sheets("Defects by Workstation Table").Range("Z2")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Path & filename & ".pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
'

End Sub

 

[Joe4]

I would guess that it isn't creating a valid path.
Also note, is there a "\" at the end of the value in cell Z1?

If not, you will want to amend this part of your formula:

 filename:=Path & filename & ".pdf"

to

 filename:=Path & "\" & filename & ".pdf"

 

[willow1985]

I would guess that it isn't creating a valid path.
Also note, is there a "\" at the end of the value in cell Z1?

If not, you will want to amend this part of your formula:

 filename:=Path & filename & ".pdf"

to

 filename:=Path & "\" & filename & ".pdf"

That was it. Thank you very much.

Just needed a 2nd pair of eyes! Thank you again

 

[Joe4]

You are welcome.

Since I cannot control whether or not they will put a "\" at the end, I often use this method instead:

Path = Sheets("Defects by Workstation Table").Range("Z1")
If Right(Path,1)<>"\" Then Path = Path & "\"

Then just keep what you originally had for the filename in your export command.
That will handle either case.