Print to PDF Macro using Custom File and Folder Name

[justanotheruser]

Hi all,

I've been reading around on the website, and all the threads that I have found have been very useful, but it seems that no-one has asked how to create a macro that allows a custom PDF file name from a cell and a customer folder name, from another cell.

I'm running Excel 2010 on Windows 7 and have two options for my printing.

a) Use CutePDF writer as a printer to save as a PDF (although from what I've read, its not easy to insert a file name there).

b) Use the in-built save as PDF now in Office 2007/Office 2010 to try this.

From what I can tell (I'm new to VBA, so I might be wrong) it maybe easier to use the latter option for my problem.

Basically, the reason that I need a custom folder name is because the sheet I am saving to PDF needs to be put in a folder according to the month it was created in. The code I have tried so far is:

Sub Make_PDF()
' Create and save .pdf
Dim pdfName As String
pdfName = Range("B7").Text
FolderName = Range("H17").Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="D:\Invoices\" + FolderName + pdfName + ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Cell B7 is an alpha-numeric code such as C0156-6302K, which seems to work fine.

Cell H17 contains the formula =NOW() which has been custom formatted to "mmm yy" - so that currently it shows "Aug 10". I have two problems:

1. When I save it currently, the file saves in D:\Invoices\ under the file name: "Aug 10C0156-6302K.pdf" as an example.
   • I would like it to be saved under D:\Invoices\Aug 10\ under the name "C0156-6302K.pdf".
2. Also, if possible I would like the user to be able to see a confirmation screen (the prompt window) so that they can confirm the file save location.

The second option isn't a must, but if its possible I'd really appreciate it.

Thank you in advance for your help!

 

[VoG]

Try

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="D:\Invoices\" & FolderName & "\" & pdfName & ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[justanotheruser]

Great! Thanks for the code VoG, it seems to work!

Do you know what code I could use to possible solve my second problem?

1. Also, if possible I would like the user to be able to see a confirmation screen (the prompt window) so that they can confirm the file save location.

Thank you in advance once again.

 

[VoG]

Try

Sub Make_PDF()
' Create and save .pdf
Dim pdfName As String, FolderName As String, FullName As String
pdfName = Range("B7").Text
FolderName = Range("H17").Text
FullName = "D:\Invoices\" & FolderName & "\" & pdfName & ".pdf"
If MsgBox("Confirm filename " & FullName & " is correct", vbYesNo + vbQuestion) = vbNo Then Exit Sub
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[justanotheruser]

Works like a charm!

I don't know if I should create another post for this, but I've got a cell, D48 which says "VAT (17.5%)".

Is it possible that I can put some sort of formula in the cell that I can then make it so if I change cell G3 (which contains "17.5%") to 20%, that the text in cell D48 will update to VAT (20%) accordingly?

Thanks again.

 

[VoG]

Try

="VAT ("&TEXT(G3,"0.0%")&")"

 

[justanotheruser]

Thanks.

I made a slight modification to your code, which after creating the invoice and opening it, gives the option for the user to open the folder location.

<CODE> Sub Make_PDF()
' Create and save .pdf
Dim pdfName As String, FolderName As String, FullName As String
pdfName = Range("B7").Text
FolderName = Range("H17").Text
FullName = "D:\Invoices\" & FolderName & "\" & 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("c:winntexplorer.exe D:\Invoices\ & FolderName", 1)
Case vbNo
End Select
End Sub </CODE>

However, when I run the code, I get a Run-time 53 error: File not found. I've tried a couple of variants on the code, but can't seem to crack it.

 

[VoG]

Try

myval = Shell("explorer D:\Invoices\ & FolderName", 1)

 

[justanotheruser]

Nope.

The code opens up C:\Users\Admin\Documents

Admin = my username

 

[VoG]

Oops.

myval = Shell("explorer D:\Invoices\" & FolderName, 1)