Excel Macro - Save PDF Error

[quick_question]

I'm trying to export my active sheet as a pdf while defining the name of the file and path as it will change each time this workbook is opened, but I continue to run into an Compile error / syntax error and can't seem to figure out where I'm going wrong -

Any help is appreciated!!

Sub SavePDF()


Dim FilePath As String
Dim DocName As String


FilePath = Sheets("PO").Range("U11").Value & ""
DocName = Sheets("PO").Range("U12").Value & ".pdf"

Worksheets("PO").Activate

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:= FilePath & "" DocName & .pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub

 

[chicagocomputerclasses]

Sub SavePDF()

It would be helpful to know what's in those cells, but for now remove all the red parts and add all the blue


Dim FilePath As String
Dim DocName As String


FilePath = Sheets("PO").Range("U11").Value & ""
DocName = Sheets("PO").Range("U12").Value & ".pdf"

Worksheets("PO").Activate

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:= FilePath & "" DocName & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub

 

[quick_question]

In between the blank "" was a \, but didn't copy over for whatever reason.

U11 = C:\Users\jem\Documents\Jobs\Job1
U12 = ABC-123_garage rennovation_20161101

 

[chicagocomputerclasses]

in that case you either have to do this

U11 = C:\Users\jem\Documents\Jobs\Job1\

or use your old line with \ for the first FilePath line.

[COLOR=#333333][COLOR=#333333]FilePath = Sheets("PO").Range("U11").Value [/COLOR][/COLOR][B][COLOR=#0000ff]& "\"[/COLOR]
[/B]

 

[Kenneth Hobson]

Looks like #1 had a path with two ""'s if it had posted right?

With a few error checks...

Sub SavePDF()
  Dim FilePath As String, DocName As String
  Dim fn As String, ws As String
  
  ws = "PO"
  If Not WorkSheetExists(ws) Then
    MsgBox "Worksheet: " & ws & _
      " does not exist in ActiveWorkbook", _
      vbCritical, "Macro Ending"
    Exit Sub
  End If
  
  With Worksheets(ws)
    FilePath = .Range("U11").Value & "\"
    If Len(Dir(FilePath, vbDirectory)) = 0 Then
      MsgBox FilePath & " does not exist.", _
        vbCritical, "Macro Ending"
      Exit Sub
    End If
    
    DocName = .Range("U12").Value & ".pdf"
    fn = FilePath & DocName
    
    .ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=fn, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False
  End With
End Sub

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function