PDF specific sheets into new created folder in Workbook location

[camerong]

Hi guys,

Need some help on the below code please. See code below.

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 "D2" and "D3", then the current date and time.

Name the folder in this order: "D2"" - ""D3"" - ""RFI Set"" - ""dd.mm.yyyy hh:mm:ss"

2. Inside this newly created folder, create another folder named "Individual Sheets"

3. Inside the folder named "Individual Sheets", run the code below so that the PDFs are generated inside.

4. Then I would like it to combine these generated PDFs into a single PDF and save that up a layer in the folder created at the start (In point "1." above). Naming the combined PDF file from the sheet cells in Sheet 1 as follows: "D2"" - ""D3"" - ""RFI Set"" - ""dd.mm.yyyy hh:mm"

Hopefully this is possible

Thanks for the help guys

Public Sub Print_all_outstanding()
  Dim i As Long
 
  Call Print_RFI_LOG_sub  'Sheet 1
 
  For i = 2 To 101
    If Sheets(i).Range("E1").Value = "NO" Then
      Call Print_to_PDF_sub(i)
    End If
  Next
DefaultMsgBox

End Sub

Sub DefaultMsgBox()

MsgBox "Process complete"

End Sub

Sub Print_to_PDF_sub(n As Long)
  Dim RFIPrefix As String, RFINum As String, JobNum As String, JobName As String, Exten As String

  RFIPrefix = "RFI "
  RFINum = Sheets(n).Range("E4") & " - "
  JobNum = Sheets(n).Range("B4") & " - "
  JobName = Sheets(n).Range("B5")
  Exten = ".pdf"
  '
  Sheets(n).ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\" & Environ("Username") & "\Downloads\" & RFIPrefix & RFINum & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

Sub Print_RFI_LOG_sub()
  Dim SheetName As String, JobNum As String, JobName As String, Exten As String
 
  SheetName = "RFI RECORD SHEET - "
  JobNum = Sheets(1).Range("D2") & " - "
  JobName = Sheets(1).Range("D3")
  Exten = ".pdf"
  '
  Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\" & Environ("Username") & "\Downloads\" & SheetName & JobNum & JobName & Exten, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

 

[John_w]

Try this. Note that ":" is an invalid character in folder and file names so I've used "." - "dd.mm.yyyy hh.mm.ss"

Option Explicit


Public Sub Print_all_outstanding()

    Dim i As Long
    Dim dt As String
    Dim mainFolder As String
    Dim sheetsFolder As String
    Dim combinedPDF As String
  
    dt = Format(Now, "dd.mm.yyyy hh.mm.ss")
    With Worksheets(1)
        mainFolder = ActiveWorkbook.Path & "\" & .Range("D2").Value & " - " & .Range("D3").Value & " - RFI Set - " & dt & "\"
        sheetsFolder = mainFolder & "Individual Sheets\"
        combinedPDF = mainFolder & .Range("D2").Value & " - " & .Range("D3").Value & " - RFI Set - " & dt & ".pdf"
    End With
    If Dir(mainFolder, vbDirectory) = vbNullString Then MkDir mainFolder
    If Dir(sheetsFolder, vbDirectory) = vbNullString Then MkDir sheetsFolder
    
    Print_RFI_LOG_sub sheetsFolder
    
    For i = 2 To 101
        If Worksheets(i).Range("E1").Value = "NO" Then
            Print_to_PDF_sub i, sheetsFolder
        End If
    Next
    
    Worksheets(1).Select
    For i = 2 To 101
        If Worksheets(i).Range("E1").Value = "NO" Then
            Worksheets(i).Select Replace:=False
        End If
    Next
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=combinedPDF, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
                                    
    Worksheets(1).Select
    
    MsgBox "Created PDFs in " & mainFolder

End Sub

Sub DefaultMsgBox()
    MsgBox "Process complete"
End Sub

Sub Print_to_PDF_sub(n As Long, sheetsFolder As String)
    Dim PDFfile As String
    
    With Worksheets(n)
        PDFfile = sheetsFolder & "RFI " & .Range("E4").Value & " - " & .Range("B4").Value & " - " & .Range("B5") & ".pdf"
    End With
    
    Worksheets(n).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                                      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

Sub Print_RFI_LOG_sub(sheetsFolder As String)
    Dim PDFfile As String
    
    With Worksheets(1)
        PDFfile = sheetsFolder & "RFI RECORD SHEET - " & .Range("D2") & " - " & .Range("D3") & ".pdf"
    End With
    
    Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                                      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

 

[camerong]

Hi John_w,

That works perfectly and was exactly what I was after! Thank you so much