Macro to print workbook to PDF

[ricdamiani]

Hi All,

I'm struggling to find a macro that prints active workbook to PDF via CutePDF. Is anyone able to help me with that?

If possible, can the macro have Dialog box so user can enter the file name and choose location? And also, if the user does not have CutePDF, can the macro send a error message such as "CutePDF not installed in this computer"?

Thanks,

[6StringJazzer]

CutePDF was made obsolete years ago by PDF export being built right into Excel. This should work for anyone with Excel 2007 or later:

Please Login or Register  to view this content.

There are also library calls for selecting the folder and file name. I would have to look up the syntax, but can follow up if the above solves your export problem.

[ricdamiani]

Thanks mate, but how do I run that? I tried with as per below and I got "Compile error: Syntax error".

Please Login or Register  to view this content.

[Alf]

Perhaps a macro like this then?

Please Login or Register  to view this content.

Macro will let you browse for folder, select file and loop through all sheets in selected file saving them as pdf files to the same folder as the file you selected. The pdf file will have the same name as the selected file + sheet number + pdf.

If file is named "Data.xlsx" and contains 4 sheets the pdf files will be named "Data.xlsxSheet1.pdf", "Data.xlsxSheet2.pdf and so forth.

Alf

[ricdamiani]

Hi Alf, thanks but that's not exactly what I need :/
The idea is to run the macro, open the dialog box to enter a name, and save as PDF file.
Is that possible?

Thanks,

[Alf]

Try this and see if this is what you wish for.

Please Login or Register  to view this content.

Alf

[ricdamiani]

Hi Alf, your macro is running the Open dialog box, instead of the Save dialog box. I am not sure what I need to do when the dialog box ask me to open a file.
The workbook is already opened and the macro is supposed to be run through the opened workbook so it can be saved as a PDF file.
Any idea?

[Alf]

can the macro have Dialog box so user can enter the file name and choose location?

Is not this what you asked for in your original post?

In the fil is open and you know the name activate it then use part of the macro to save it as a pdf file and finally you can use the "Kill" command to delete the original file after you have closed it. You need to give the full path + file name for the "Kill" command.

Alf

[ricdamiani]

Is not this what you asked for in your original post?Alf

Not really, sorry about that.

The logic is:
1- user run the macro called "SAVE AS PDF"
2- SAVE dialog box will open. User enter the name of PDF file that is about to be saved, and its location.
3- User clicks the Save button from the dialog box

No need to delete original file. In fact, user need the original file.

Cheers,

Attachment 619996

[Alf]

Ok Last try

Please Login or Register  to view this content.

Macro asks first for new file name, don't add .pdf.

It then gives you a choice which folder to use. After macro finished pdf file is active and shown. If you don't wish to see the pdf after macro run change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

[ricdamiani]

Hi Alf, thanks mate it looks good but a couple of things:

- when it prompts to enter the file name, if you click "Cancel" it actually doesn't cancel the process and it goes to the "choose folder" dialog box. Is it possible to cancel/stop the macro if the user chooses "cancel"? Perhaps with a message "Action cancelled, PDF file not created"?

- I'm getting the invalid error "Run-time error 5: invalid procedure call or argument" and the debug is highlighting in yellow the lines below. I have Excel 2007 and when I click "Save As" and "other formats", I cannot see the PDF extension option there. Maybe that's why I have this error? Because my Excel cannot generate PDF files? If that's the case, if there is an error when running the macro and the user cannot generate the PDF, can the macro stop and send a message to the user such as "PDF not generated, your Excel might not be able to generate a PDF file", instead of generating a macro error?

Please Login or Register  to view this content.

Cheers

[Alf]

I have Excel 2007 and when I click "Save As" and "other formats", I cannot see the PDF extension option there

So right you are as excel 2007 con't do this right out of the box.

Before you can save files in the PDF file format, you must download and install the Save as PDF or XPS Add-in program from the Microsoft Web site.

https://www.microsoft.com/en-us/down...ails.aspx?id=7

As your forum profile says Excel 2010 you better modify this to reflect the program you really use i.e. Excel 2007.

Modify macro to:

Please Login or Register  to view this content.

As for selecting a folder, if you click "Cancel" macro stops but if no folder is selected and you click "OK" macro will run and place pdf file in same folder as the excel file is stored in.

Alf

[ricdamiani]

Thanks Alf, works well but if someone else runs that macro, with Excel 2007 & without the PDF add-in, they will get an error and the debug screen will open.
Is there a way to include in the code an error message instead of opening the debug screen? Error message could be "your Excel cannot save as PDF file".

Cheers,

[Alf]

Is there a way to include in the code an error message instead of opening the debug screen?

Not as far as I know. Users could always check if the "Save As"contains the PDF reference if so it will work and if not they should download the Microsoft PDF/XPS add-in.


Alf