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

ulpk29

New Member
Joined
Mar 6, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Code:
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

New Member
Joined
Mar 6, 2019
Messages
6
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

New Member
Joined
Mar 6, 2019
Messages
6
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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.
Code:
fn = ThisWorkbook.Path & "\PDFs\" & _
    CreateObject("Scripting.FileSystemObject").GetBaseName(ThisWorkbook.Name)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top