Print to PDF macro +custom filename +custom folder and subfolder

Allram

New Member
Joined
Apr 18, 2018
Messages
2
Hello,

I'm having trouble getting a macro i found here on this site to work, i have tried to do some modifications, but VB is certainly not my strong side...

This is the macro:
Code:
Sub Make_PDF()' Create and save .pdf
Dim pdfName As String, FolderName As String, SubFolderName As String, FullName As String
pdfName = Range("C3").Text
FolderName = Range("C1").Text
SubFolderName = Range("C2").Text
If Not DirExists("C:\Invoices\" & FolderName & "\" & SubFolderName & "\") Then MkDir "C:\Invoices\" & FolderName & "\" & SubFolderName & "\"
FullName = "C:\Invoices\" & FolderName & "\" & SubFolderName & "\" & pdfName & ".pdf"
If MsgBox("Please confirm that name and location is correct: " & FullName & ".  -  " & " Is it correct?", vbYesNo + vbQuestion, "Confirm File Name and Location") = vbNo Then Exit Sub
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
YesNo = MsgBox("Would you like to open the folder where the invoice was saved?" _
, vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("explorer C:\Invoices\" & FolderName & "\" & SubFolderName & "\", 1)
Case vbNo
End Select
End Sub




Function DirExists(sSDirectory As String) As Boolean
If Dir(sSDirectory, vbDirectory) <> "" Then DirExists = True
End Function

I found it here: https://www.mrexcel.com/forum/excel...df-macro-using-custom-file-folder-name-2.html

The thing is that i need the macro to save the file in a subfolder (SubFolderName = C2")
Foldername is C1
Filename is C3 in the excel file.

When the directory does not exist i get an error saying the "Runtime Error 76, the path is not found" and this part is marked Yellow in the VB editor:
Code:
MkDir "C:\Invoices\" & FolderName & "\" & SubFolderName & "\"

Anyone got any tips for me here? :)
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try
Code:
Foldername = Range("C1").Text
SubFolderName = Range("C2").Text
If Not DirExists("C:\Invoices\" & Foldername) Then MkDir "C:\Invoices\" & Foldername
If Not DirExists("C:\Invoices\" & Foldername & "\" & SubFolderName & "\") Then MkDir "C:\Invoices\" & Foldername & "\" & SubFolderName & "\"
FullName = "C:\Invoices\" & Foldername & "\" & SubFolderName & "\" & pdfName & ".pdf"
 

Allram

New Member
Joined
Apr 18, 2018
Messages
2
Hi & welcome to MrExcel.
Try
Code:
Foldername = Range("C1").Text
SubFolderName = Range("C2").Text
If Not DirExists("C:\Invoices\" & Foldername) Then MkDir "C:\Invoices\" & Foldername
If Not DirExists("C:\Invoices\" & Foldername & "\" & SubFolderName & "\") Then MkDir "C:\Invoices\" & Foldername & "\" & SubFolderName & "\"
FullName = "C:\Invoices\" & Foldername & "\" & SubFolderName & "\" & pdfName & ".pdf"

Thanks a ton! Have been trying to wrap my head around this problem for days.
Really, thanks a lot :biggrin:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top