Print to PDF into created folder in Workbook location

[camerong]

Hi guys,

Need help on this one please, not sure how to go about doing it. I have the code below that I have been using up until now which prints the active sheet to PDF in the Downloads folder.

What I would like it to do is to do the following, in sequence:

1. Create a new folder in the location of the Workbook. Name this folder based on the contents of worksheet cells "B4" and "B5", then the current date and time.
Name the folder in this order: "B4"" - ""B5"" - ""dd.mm.yyyy hh:mm:ss"

2. Inside this newly created folder, run the code below so that the PDF is generated inside.

Thanks for the help guys

Sub Print_to_PDF()
  '
  ' PDF_Single_Page Macro
  ' Saves to PDF Ctrl+Shft+End page selection area
  '
  RFIPrefix = "RFI "
  RFINum = Range("E4") & " - "
  JobNum = Range("B4") & " - "
  JobName = Range("B5")
  Exten = ".pdf"
  '
  
  With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .CenterVertically = False
    .PaperSize = xlPaperA4
    .BlackAndWhite = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  Application.PrintCommunication = True
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\" & Environ("Username") & "\Downloads\" & RFIPrefix & RFINum & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

 

[DanteAmor]

Hi.

Some notes:
1)

1. Create a new folder in the location of the Workbook.

Does that mean create the folder in the folder where you have the file with the macro?
If so, then that's what this line is for:

  newFolder = ThisWorkbook.Path & "\" & JobNum & JobName & " - " & Format(Now, "dd.mm.yyyy hh.mm.ss")

2)

hh:mm:ss

You can't use a colon ":" for the folder name, so I used a dot "."

----- --
Try this:

Sub Print_to_PDF()
  '
  ' PDF_Single_Page Macro
  ' Saves to PDF Ctrl+Shft+End page selection area
  '
  Dim RFIPrefix As String, RFINum As String, JobNum As String, JobName As String, Exten As String
  Dim newFolder As String
 
  RFIPrefix = "RFI "
  RFINum = Range("E4").Value & " - "
  JobNum = Range("B4").Value & " - "
  JobName = Range("B5").Value
  Exten = ".pdf"
  '
  newFolder = ThisWorkbook.Path & "\" & JobNum & JobName & " - " & Format(Now, "dd.mm.yyyy hh.mm.ss")
 
  With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .CenterVertically = False
    .PaperSize = xlPaperA4
    .BlackAndWhite = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  Application.PrintCommunication = True
 
  If Dir(newFolder, vbDirectory) = "" Then
    MkDir newFolder
  End If
 
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=newFolder & "\" & RFIPrefix & RFINum & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[camerong]

Thanks Dante, I'll give it a try

Also, yes I mean create the folder in the folder where I have the Excel file with the macro

 

[camerong]

Thanks Dante, that works fantastic!

That's exactly what I was after