Saving PDF works great, I just want to drop the Excel extension.

[ulpk29]

My file is saving the entire name when it converts to a PDF. I am simply trying to get the .xlsb part to drop off. Can anyone help me understand how to code the removal of the .xlsb from the Filename or Populate the save as name as one I choose?
I want to use this base code.

Sub SaveSheetsasPDF()

ThisWorkbook.Sheets(Array("Page <1>", "Page <2>")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\PDFs" & ThisWorkbook.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

 

[Kenneth Hobson]

Sub SaveSheetsasPDF()
  Dim fn$
  
  fn = ThisWorkbook.Path & "\PDFs" & _
    CreateObject("Scripting.FileSystemObject").GetBaseName(ThisWorkbook.Name)
  
  ThisWorkbook.Sheets(Array("Page <1>", "Page <2>")).Select
  
  ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, Filename:=fn, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

  Worksheets("Page <1>").Select
End Sub

 

[ulpk29]

Thank you. I adjusted it slightly to get the title I am seeking. I'm also trying to get the file to save in the save path where the file is located just in a file inside that location. They are fluid for each quarters reporting.

For example. the file will originate in
Z:\2018\2018_12_Dec\Fact Sheet

I run the PDF vba and I want to save the file in
Z:\2018\2018_12_Dec\Fact Sheet\PDFs

I thought this would work, but it doesn't appear to function properly.

 

[ulpk29]

Sub SaveSheetsasPDF()
Dim fn$
Dim ChDir As String


fn = ThisWorkbook.Path & _
CreateObject("Scripting.FileSystemObject").GetBaseName(ThisWorkbook.Name)

ThisWorkbook.Sheets(Array("Page <1>", "Page <2>")).Select

ChDir = ThisWorkbook.Path & "\Fact Sheet\PDFs"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=fn, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

Worksheets("Page <1>").Select
End Sub

 

[Kenneth Hobson]

When posting code, please paste between code tags. Click the # icon on reply toolbar to insert the tags.

I guess you mean that PDFs is a subfolder? If so, then you will need to create it if it does not exist. If it does exist, the ChDir() method or the more absolute method would work. e.g.

fn = ThisWorkbook.Path & "\PDFs\" & _
    CreateObject("Scripting.FileSystemObject").GetBaseName(ThisWorkbook.Name)