Save as PDF, add month folder

[Broflovski]

Hi guys,

I'm a noobie with excel VBA but trying to learn, and i hope u guys could help me out.

So the code so far is saving the sheet as PDF to the current year folder, but i want to add a month folder into the year folder.
I tried a lot but get a lot of errors during adjusting the code

For ur info this code isn't written by myself only adjusted.

Thanks in advance!

Sub savePDF():

Dim dte As Date
Dim numericalDate As Integer
Dim sourceDir As String
Dim year As Integer
Dim reportWs As Worksheet
Dim folder_exists As String
Dim fullFileName As String
Dim pdfFileName As String
Dim folderPath As String
Dim filePart As String

    'set worksheet as current
    Set reportWs = Worksheets("Lege Factuur")
    
    ''OR
    ''set reportWs = worksheets("Worksheet_name")
    
    'get year
    year = Trim(Str(Format(Date, "yyyy")))
    
    'get date
    dte = Now()
    
    'get numerical date
    numerical_date = Int(CDbl(dte))
    
    'source directory
    sourceDir = "E:\Paul\Facturen\"
    
    'check if folder exists, if it doesnt them create a new directory
    folder_exists = Dir(sourceDir & "\" & year, vbDirectory)
    If folder_exists = "" Then
        MkDir sourceDir & "\" & year
        folder_exists = Dir(sourceDir & "\" & year, vbDirectory)
    End If
    
    'get folder path
    folderPath = sourceDir & "\" & folder_exists
    
    'get filename (I dont think you should use the DATE and TIME as you have as the characters are invalid) Please change below format as you see fit.
    filePart = reportWs.Range("C14").Value & reportWs.Range("D14").Value & " " & reportWs.Range("B2").Value & " " & reportWs.Range("D18").Value
    fullFileName = filePart & " " & Format(Now(), "dd-mm-yyyy")
    
    'PDF save locaiton
    pdfFileName = folderPath & "\" & fullFileName
    
    'Save PDF
    reportWs.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

 

[John_w]

Try this macro.

Public Sub Save_PDF_In_Year_Month_Subfolder()

    Dim sourceDir As String
    Dim reportWs As Worksheet
    Dim folder_exists As String
    Dim fullFileName As String
    Dim pdfFileName As String
    Dim folderPath As String
    Dim filePart As String
    Dim yearSubfolder As String, monthSubfolder As String

    'set worksheet as current
    Set reportWs = Worksheets("Lege Factuur")
    
    'source directory
    sourceDir = "E:\Paul\Facturen\"
    
    yearSubfolder = sourceDir & year(Date) & "\"
    monthSubfolder = yearSubfolder & MonthName(Month(Date), Abbreviate:=True) & "\"
    
    'check if year subfolder exists, if it doesn't then create a new folder
    folder_exists = Dir(yearSubfolder, vbDirectory)
    If folder_exists = vbNullString Then MkDir yearSubfolder
    
    'check if month subfolder exists, if it doesn't then create a new folder
    folder_exists = Dir(monthSubfolder, vbDirectory)
    If folder_exists = vbNullString Then MkDir monthSubfolder
    
    'get filename (I dont think you should use the DATE and TIME as you have as the characters are invalid) Please change below format as you see fit.
    filePart = reportWs.Range("C14").Value & reportWs.Range("D14").Value & " " & reportWs.Range("B2").Value & " " & reportWs.Range("D18").Value
    fullFileName = filePart & " " & Format(Date, "dd-mm-yyyy") & ".pdf"
    
    'PDF save location
    pdfFileName = monthSubfolder & "\" & fullFileName
    
    'Save PDF
    reportWs.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub