VBA Export As PDF & Create Folder If Does Not Exist

[Lewzerrrr]

Hey,

So I managed to create the following code to save a folder using 4 different variables for the file path and file name.

But firstly I need to check if a folder/filepath exists, if not to create one e.g. C:\Users\Tiana\Documents\Branch Merchandising\SS17\Branch Stock Report\Week 49.. so check if week 49 exists, if it does carry on, if not create a week 49 folder.. or week 50 etc etc. and then once it's saved to then export Sheet(1) as PDF.

Greatly appreciate all the help even if it's to tidy my existing code up!

Thanks,

Sub SaveAsAndExport()

Dim FPath As String
Dim Season As Range, Yr As Range, Wk As Range, Day As Range


Set Season = ActiveWorkbook.ActiveSheet.Range("M1")
Set Yr = ActiveWorkbook.ActiveSheet.Range("M2")
Set Wk = ActiveWorkbook.ActiveSheet.Range("M3")
Set Day = ActiveWorkbook.ActiveSheet.Range("M4")


Application.DisplayAlerts = False


FPath = "C:\Users\xxxxx\Documents\Branch Merchandising\" & Season & Yr & _
        "\Branch Stock Report\Week " & Wk & "\"


ActiveWorkbook.SaveAs FPath & Season & Yr & " " & "Week " & Wk & " " & Day & " - Branch Stock Report" & ".xlsm"


End Sub

 

[Lewzerrrr]

Okay so I've figured out how to save it as pdf

Just need help on checking if a folder exists, if not create using "Week " & wk..

Sub SaveAs()

Dim FPath As String, FName As String
Dim Season As Range, Yr As Range, Wk As Range, Day As Range


Set Season = ActiveWorkbook.Sheets(2).Range("M1")
Set Yr = ActiveWorkbook.Sheets(2).Range("M2")
Set Wk = ActiveWorkbook.Sheets(2).Range("M3")
Set Day = ActiveWorkbook.Sheets(2).Range("M4")


Application.DisplayAlerts = False


FPath = Environ("Userprofile") & "\Documents\Branch Merchandising\" & Season & Yr & _
        "\Branch Stock Report\Week " & Wk & "\"


FName = Season & Yr & " Week " & Wk & " " & Day & " - Branch Stock Report"


ActiveWorkbook.SaveAs FPath & FName & ".xlsm"


ThisWorkbook.Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & FName & ".pdf" _
    , openafterpublish:=True
    
MsgBox FName & " was saved and exported to PDF."


End Sub

 

[John_w]

This checks if the FPath folder exists and creates it if not:

    If Dir(FPath, vbDirectory) = vbNullString Then
        MkDir FPath
        MsgBox "Created folder " & FPath
    End If

 

[Lewzerrrr]

This checks if the FPath folder exists and creates it if not:

    If Dir(FPath, vbDirectory) = vbNullString Then
        MkDir FPath
        MsgBox "Created folder " & FPath
    End If

Thanks John_w really helpful. I found out that it needs to look at the last folder to create one and as I'm using many variables which would all need to create folders I've constructed 3 different ifs, how can I nest these together?

    'Create Season folder if not existIf Len(Dir(PPath, vbDirectory)) = 0 Then
    MkDir (PPath)
End If


    'Create Branch Stock Report folder if not exist
If Len(Dir(BSR, vbDirectory)) = 0 Then
    MkDir (BSR)
End If


    'Create Week folder if not exist
If Len(Dir(FPath, vbDirectory)) = 0 Then
    MkDir (FPath)
End If

 

[John_w]

You shouldn't need to nest the Ifs: just check the folders in sequence as that code is doing.

 

[Lewzerrrr]

I guess what I mean is how can I make the code look tidier?