Message Box Then Save File in Directory (Create if Inexistant)

[jay_the_cob]

I am a beginner at VBA and am trying to create a button that will open a message box notifying the user he is about to save a pdf copy of the workbook. If Yes, it proceeds to verifying whether the dynamic directory exists (creates if not), then saves the file there. If No, simply does nothing.

My message box is working fine, and the button attempts to save the file, but it is unable to create the directory since it does not currently exists.

All help is appreciated.



Sub GetAnswerAndSavedPDF()

Dim Msg As String, Ans As Variant

Msg = "Do you wish to proceed with saving a PDF backup of this Oversight Dashboard?'"

Ans = MsgBox(Msg, vbYesNo)

Select Case Ans

Case vbYes

If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY")) Then
MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY"))
End If

ThisWorkbook.Sheets(Array("Oversight", "KPI Tracking")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY") & "\Oversight Report - " & Format(Now(), "MM.DD.YYYY") & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.DisplayAlerts = False

Case vbNo
GoTo Quit:

End Select

Quit:

End Sub
Public Function FileFolder(strFULLPath As String) As Boolean
If Not Dir(strFULLPath, vbDirectory) = vbNullString Then FileFolder = True
On Error GoTo 0
End Function


Thanks,

 

[Fluff]

Hi & welcome to MrExcel.
Difficult to tell what's wrong due to the way you've supplied the code, but try this for your function

 Public Function FileFolder(strFULLPath As String) As Boolean

 If Not Dir(strFULLPath, vbDirectory) = vbNullString Then FileFolder = True
 
 End Function

 

[jay_the_cob]

Hi & welcome to MrExcel.
Difficult to tell what's wrong due to the way you've supplied the code, but try this for your function

 Public Function FileFolder(strFULLPath As String) As Boolean

 If Not Dir(strFULLPath, vbDirectory) = vbNullString Then FileFolder = True
 
 End Function

Sorry... thought that came out a bit unclear.

This in the code as I have it written out.

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Sub GetAnswerAndSavedPDF()[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Dim Msg As String, Ans As Variant[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Msg = "Do you wish to proceed with saving a PDF backup of this Oversight Dashboard?'"[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Ans = MsgBox(Msg, vbYesNo)[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Select Case Ans[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Case vbYes[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY")) Then[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]    MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY"))[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]End If[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]ThisWorkbook.Sheets(Array("Oversight", "KPI Tracking")).Select[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]        "\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY") & "\Oversight Report - " & Format(Now(), "MM.DD.YYYY") & ".pdf" _[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]        :=False, OpenAfterPublish:=False[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]        Application.DisplayAlerts = False[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Case vbNo[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]GoTo Quit:[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]End Select[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Quit:[/SIZE][/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]End Sub[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]Public Function FileFolder(strFULLPath As String) As Boolean[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]If Not Dir(strFULLPath, vbDirectory) = vbNullString Then FileFolder = True[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]On Error GoTo 0[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial][FONT=Calibri][SIZE=2]End Function


[/SIZE][/FONT][/FONT][/COLOR]

 

[Fluff]

Do all the sub directories exist?
Also do you get any errors, if so what is the error message & what line is highlighted when you click Debug?

 

[jay_the_cob]

Do all the sub directories exist?
Also do you get any errors, if so what is the error message & what line is highlighted when you click Debug?

Hi,

The directory \\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY") does not exist (I see I was missing a few "" when I pasted it but in my original code they appear). I would like the code to create it

The message box appears, then I click Yes. It returns a Run-time error '76' Path not found, then points at this line when I debug:

MkDir (\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & Format(Now(), "YYYY") & "\" & Format(Now(), "MMMM YYYY"))

If I remove this section - \" & Format(Now(), "YYYY") & "\" & Format(Now(), "MMMM YYYY"), it no longer errors out on my MkDir, but now on my ActiveSheet.ExportAsFixedFormat code.

I feel as though the \" & Format(Now(), "YYYY") & "\" & Format(Now(), "MMMM YYYY") is causing the issue, but unsure how to go about fixing that as I need the file saved daily without having to change the date daily in my code.

Thanks again

 

[Fluff]

Do all these folders exist \\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\

 

[jay_the_cob]

Do all these folders exist \\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\

Yes they do

 

[Fluff]

Just noticed it looks like you are trying to create two directories.
You'll need to create \\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & Format(Now(), "YYYY")
and then create the subfolder.

 

[jay_the_cob]

Just noticed it looks like you are trying to create two directories.
You'll need to create \\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & Format(Now(), "YYYY")
and then create the subfolder.

Oh great, that makes sense.

How could I code it so it will create the first directory, then the next? I test by creating the year folder and then ran the code and it works as I need.

 

[Fluff]

You'll need to do it twice like

If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & format(Date, "YYYY")) Then
    MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST" & format(Date, "YYYY"))
End If
If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & format(Date, "YYYY") & "\" & format(Date, "MMMM YYYY")) Then
    MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\TEST\" & format(Date, "YYYY") & "\" & format(Date, "MMMM YYYY"))
End If

As you are only interested in the date rather than date & time I've replaced the Now() with Date