Need help saving PDF name as the file's name

[CRichards]

Hello,

I am super super new to VBA. My boss wanted me add a macro to a file where certain tabs are selected and create a PDF. Looked it up and I'm good there.. Now I need help just saving the file as the excel file name (excluding the excel ending)...

This is what I'm working with.

Sub ExportAsPDF()


Dim FolderPath As String


FolderPath = "Z:\Analysis\_PDF\Filename"



Sheets(Array("Executive Summary", "Approval", "PA - Summary", "BA")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath, _
openafterpublish:=True, ignoreprintareas:=False

MsgBox "All PDF's have been successfully exported."


End Sub

The file name keeps on being called "Filename". I want this to be called the name of the excel file (excluding the excel ending).


Thank you!!!

 

[shg]

Try this:

Sub ExportAsPDF()
  ' note the trailing \:
  Const sPath       As String = "Z:\Analysis\_PDF\"
  Dim sFile         As String

  With ActiveWorkbook
    sFile = Left(.Name, InStrRev(.Name, ".")) & "pdf"
    .Sheets(Array("Executive Summary", "Approval", "PA - Summary", "BA")).Select
  End With
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                  Filename:=sPath & sFile, _
                                  OpenAfterPublish:=True, _
                                  IgnorePrintAreas:=False

  MsgBox "All PDFs have been successfully exported."
End Sub

 

[CRichards]

Worked like a charm... Thank you!!

 

[shg]

You're welcome.