VBA code to open SAVE AS dialogue box with Save As Type: PDF

Question

Hi,

I am looking for a VBA code to open the SAVE AS dialogue box with the suggested file type of "PDF".

I will then assign this macro to a rectangle shape that will serve as a button called "SAVE AS PDF" which the user can click to open the SAVE AS dialog box.

(I am only wanting to save the current worksheet to PDF, not the whole workbook).

Answer 1

BTW

If you try the macro as in my first post the pdf files will be saved on the same folder the workbook you are currently using is.

If you want a different path then use the last code I sent you

Answer 2

I meant like this

*******************************************************************

Sub SaveAsPDF()

Dim fName, MyPath, currDate As String

MyPath = "C:\Users\Darren\Desktop"

fName = "Daily Worksheet"

currDate = Format(Now, "dd.mm.yyyy-hhmmss")

With ActiveSheet

    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyPath & fName & currDate & ".pdf", _

    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End With

End Sub

************************************************************************************

This macro will save on your desktop a .pdf file with this name pattern

ex. "Daily Worksheet-28.05.2020-174548.pdf"   indicating the date and time the document was saved

If you want to change the name to "Sales" then

Change fName = "Daily Worksheet"

with fName = "Sales"

Therefore your pdf files will be stored under the name, ex. "Sales-28.05.2020-194548.pdf" indicating the Date and Time it was saved.

I hope this macro will help you 

Regards

Answer 3

Hi -

Please try to add a comma between arguments, for example:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:="sales.pdf", Quality:=xlQualityStandard, OpenAfterPublish:=True

Note that this will not open the dialog but will save the file directly according to the arguments.

Does this help your question? If yes, would you mark it as resolved and give a rating? Otherwise please let me know how I can further help you.

Thank you!

Answer 4

Hi Darren,

You would need to use Workbook.ExportAsFixedFormat method. More detailed documentation can be found here: https://docs.microsoft.com/en-us/office/vba/api...

Does this help your question? If yes, would you mark it as resolved and give a rating? Otherwise please let me know how I can further help you.

Thank you!

Answer 5

Hi Elvissey,

Thank you so much for the advice. However, I am unable to make it work....