Printing to PDF

**Grouty** · 12-10-2011, 11:59 PM

Hi Guys

Just upgraded to Excel 2011 for Mac and have discovered that VBA is back!! Yea! So, now I am trying to use it but have run into a problem. I have set the print area on a worksheet and want to write a macro that will:

Copy and paste a value from one cell to another (this part I can do) then print the "print area" to a PDF. I want to be able to email this PDF file to a client.

If I set "record macro" going, with the cell I want to copy from already selected, the result is this:

Please Login or Register  to view this content.

However, rather than either printing to PDF or bringing up the print dialog box, just prints the "print area" on paper.

As part of my wish list I'd love it to:

Copy & paste a value from 1 cell to another
Bring up the Print Dialog box
Select "Save as PDF..." from the drop down menu
Give the file a name, as copied from the cell earlier, with today's date appended
Save the file to a specific location.

However, at this point I'd settle, very happily, for just getting the macro to bring up the print dialog box instead of printing. :-) Then I could do the rest manually.

Any and all suggestions received with gratitude. :-)

Cheers,

Grant

**Grouty** · 12-11-2011, 07:53 PM

I thought I had found a solution to this. If I manually select "Layout" from the ribbon then press the "Preview" button then a page with the relevant data opens in the Preview application that comes with the Mac. All I would have to do is hit "Save" and give the file a name. However, Life is not meant to be easy. When I try to record these actions using the macro recorder I get the following code:

Please Login or Register  to view this content.

If I paste this code into the end of my macro so that I get:

Please Login or Register  to view this content.

and try to run it I get a runtime error.

I have tried changing the destination from:

Please Login or Register  to view this content.

to a folder on my hard drive e.g.: Macintosh HD:Users:gmc:invoices where I create the folder "invoices" at that location but I get the same error again.

Perhaps someone here can see a very obvious mistake I am making? As always, any and all advice is gratefully received.

**Grouty** · 12-12-2011, 06:28 AM

Isn't the Internet a wonderful thing? I can hardly believe there are so many clever and helpful people out there. I continued my search for an answer to the printing to PDF issue on a Mac and I have it working thanks to a guy in Holland. His name is Ron de Bruin and he has just started using a Mac so he is posting stuff on his web site regarding issues he finds. How good is that!

Anyway, I had a look at the rules and I don't think I'm breaking any by posting the code he has on his web site. He even has a workbook containing the code and examples so you can test it out. His web site is: http://www.rondebruin.nl/mac.htm and the section I wanted was right at the bottom of the page.

Please Login or Register  to view this content.

You'd be much better going to look at the web site though as I'm sure there is other stuff you need to make it work. In the end I had to export something called "CodeMod" from his workbook and import it into mine. Then it all worked beautifully.

Thank you to everyone who looked at my problem and even thought about how to solve it. You are all great. I hope Ron de Bruin gets some Kudos for his contribution here. I'll look for a way to let him know I've been talking about him here.