Saving File as pdf with Macro

[domgee]

Hi All,

Simple question (I hope). Just want to save a worksheet as pdf with the click of a button. The file should have date (cell F1), invoice number (cell F7) and Customer Name (cell B7).

Hoping someone can help me with this asap.

Thanks.

[Solus Rankin]

I know its three clicks, but whats wrong with File tab->Save & Send->CreatePDF?

Are the three cells you mention in your post already populated? Intentionally left blank to receive the values you specified?

[TMS]

Something like:

Please Login or Register  to view this content.

Regards, TMS

[domgee]

I'm still having trouble. Where exactly in the code do I put what folder I want to save the pdf file?

ie. I'm using a mac and the location of the files should be saved here: /Users/domgee/Documents/Invoices

Sorry I am not too savvy when it comes to this. Thanks for all your help

[TMS]

In the example, I have set the path (sReportPath) to be the same as the Workbook's path.

In your case, it looks as though you'd change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Regards, TMS

[domgee]

Thanks for the reply but nothing happens when I click the button. This is my exact coding:

Sub SaveInvoice()
sReportPath = "/Users/domgee/Documents/Invoices"
sReportName = Range("F1").Value & " " & Range("F7").Value & " " & Range("B7").Value
On Error Resume Next
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:= _
sReportPath & _
Application.PathSeparator & _
sReportName & _
".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
End Sub

Is something missing?

[TMS]

Have you assigned the macro to the button? Have you enabled macros? Can you step through the code using F8?

Suggest that you post a sample workbook.

Regards, TMS

[GeneralDisarray]

I put this in a project recently, and this is all that was needed. But, I wanted the same name. You could come up with a different file name for the variable 'fName'. I wanted the same name with a different extension, in the same directory.

.fullname give you the workbook path and file name, if you want another directory to be used just write it in place of my formula on the right hand side of the equal sign. Also, using values from the sheet in the file name can cause something like this to fail if those cells contain non-valid file name characters.

Please Login or Register  to view this content.

[domgee]

I will try your suggestion but in the meantime here is the uploaded worksheet. I have assigned the Macro and Macros are enabled.

[TMS]

Three problems. 1) The workbook does not have any code in it and, consequently, 2) the button does not have a macro assigned to it. And 3) the cells that are used to build the file name are empty so the code cannot save a file with just a couple of spaces as the file name ... difficult to find, I guess.

This code works for me. It is, however, set up for testing so you'll need to adjust it.

Please Login or Register  to view this content.

See the attached amended workbook.

Regards, TMS