Save as PDF using function?

[Prevost]

Hi,
I have the code below that saves the worksheet as a pdf with a certain name in a certain folder. I have this code several times throughout my macro and so my question is, can I make this into a function (and how I would do that) that I can call to make my code much neater? Also, if I wanted to change the parameters, such as the PDF name and file location, how would I go about performing that?
Thanks!

One more thing, I am unsure of when to use a Class or a Function, or if this is a case where I could use either. Any explanation would greatly help.

Sub Make_PDF()
'this names the file the value of C7
'also specifies the location of the saved pdf file
Dim pdfName As String
pdfName = Range("C7").Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\100043059\Desktop\" & pdfName & ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[JRS1010]

I don't think there is any reason to change it from a sub, so, assuming you already have the code you posted as a sub in your workbook somewhere (and it appears that you do), you can call it in your macro like this:

Sub test()
'....other code not related to making PDFs
Call Make_PDF
'...more code not related to making PDFs
End Sub

And if you want to change the file name and/or file path, you could use the following modification of your code and just change the "pdfName" and "fPath" sections near the top (note: this modification no longer depends on the value of cell "C7", it would now come directly from the sub).

Sub Make_PDF()
Dim pdfName, fPath As String

pdfName = "Your PDF Name" 'Change text within quotes to desired file name
fPath = "C:\Users\100043059\Desktop\" 'Change text within quotes to desired file path

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

End Sub

 

[Prevost]

Yes I could call the sub to make the pdf. I could declare a public variable to pull into that sub to update the directory and the pdf name whenever I call the Make_PDF sub. What I would like to do is to enter in the pdf name and directory when I call the Function(pdfname, pdfdirectory) to avoid declaring those global variables. (I didn't really think to call it as a Sub so thank you).

Would there be a way to create it as a function?

[FONT=sans-serif][SIZE=2]Public PDFName As String[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]Public PDFDirectory As String[/SIZE][/FONT] 

[FONT=sans-serif][SIZE=2]Sub SomeCode()[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]    'put code in here[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]    'PDFName =  new variable[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]    'PDFDirectory = new variable[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]    Call Make_PDF[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]    'put other code in here[/SIZE][/FONT] 
[FONT=sans-serif][SIZE=2]End Sub[/SIZE][/FONT]

 

[Prevost]

I have not tried it yet but I thought that maybe I could call the Make_PDF sub procedure but pass it with arguments which would then assign the proper values for the PDF name and the file directory.

Sub Make_PDF(PDFDirectory as String, PDFName as String) ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFDirectory & PDFName & ".pdf" _ , Quality:=xlQualityMedium, IncludeDocProperties:=False, _ IgnorePrintAreas:=False, OpenAfterPublish:=True End Sub

'and then pass it with arguments, either an assigned variable or a string

Sub Call_PDF_Pring()
'some code here
Call Make_PDF("Desktop","My PDF Name")
'more code here
End sub

</pre>

 

[JRS1010]

OK, I see what you want. The code below worked for me (paste it into a Module).

Public pdfName, fPath As String
Function Make_PDF(pdfName As String, fPath As String)
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & pdfName & ".pdf" _
    , Quality:=xlQualityMedium, IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End Function

 

[Prevost]

Thanks!