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!

 

[justanotheruser]

OK, fixed that - using the code I just posted, how would I make this a separate macro, I tried and get a 1004 runtime error - unable to set the text property of the Characters class.

 

[VoG]

Code like this should work

Sub CopyText()
Sheets("Sheet2").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text
Sheets("Sheet4").Shapes("TextBox 2").TextFrame.Characters.Text = Sheets("Sheet3").Shapes("TextBox 1").TextFrame.Characters.Text
End Sub

 

[justanotheruser]

Same error message as before.. :S

 

[VoG]

Where are you putting the code. It has to go in a regular module not a sheet code module.

 

[justanotheruser]

I went to Insert - Module and pasted the code you gave, and that's where I got the message - it's in a new module called Module 2, whereas Module 1 has the other macro.

 

[VoG]

I don't know why you are getting an error. I tested the code that I originally posted for copying the text box text as a stand alone macro and it worked without error.

 

[justanotheruser]

Solved it - I had protected the sheet, and thus locked editing of the text in the shape - unchecked the box and hey presto!