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

Please try the code below

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

Sub SaveAsPDF()

Dim fName, MyPath, currDate As String

MyPath = ThisWorkbook.Path & ""

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

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

Notes:

1- Change the MyPath and fName as per your needs, but DO NOT forget the "" at the end

2- Your active sheet will always be saved as ".pdf" in the following format

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

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 5

Hi Jeovany,

Many thanks for that! Do you mean like this?

Sub SaveAsPDF()

Dim fName, MyPath, currDate As String

MyPath = ThisWorkbook.Path &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