Export a Selection as a PDF

Hi I am running Excel 2011 on a Mac. I had a small macro that printed the result of a worksheet as a PDF file. Something has happened And it no longer works. Did have major crash a while ago but everything seems to be working and to be sure I tried to run it on an older iMac same problem.I stalls on the last line. In the beginning the error was the " index to the specified collection is out of bounds." Now i get Application or object defined error

Please Login or Register  to view this content.

Running the watch window would seem to suggest that I have the correct address. As hopefully this line shows.
: myoutput : "Macintosh HD:Users:mitch06042016:Dropbox:Quantum:Deposits:2019 Deposits:testqs.pdf" : Variant/String

At one stage the error was the " index to the specified collection is out of bounds."

Any help very much appreciated .

Peter

Hi

Thanks for taking the trouble to get back to me but that is not the problem. As you can see from the code I have a msgbox before the last line that confirms I have correctly identified the LRow & LCol. In fact the reason I posted the line from the watch window which was to confirm the correct output. As it happens the PDF print is the last step in a long procedure and the call to the function LastCell occurs many times. I know the watch window shows the line as a variant/string I had in my testing set both myPath and myoutput as strings and changed it in case it was the problem.

Think I am missing something I added a debug statement after PrintA is set and it shows the correct range.The selection command correctly selects the range.

Interestingly I rewrote the sub from scratch on my iMac and it worked at first. however it would not fit the output to one page and when I tried again to do that today it stopped working, very strange. Some research on the web suggest it maybe a bug in 2011.
So I wrote an Applescript routine to do the same thing and it had a problem as it printed all the sheets instead of the one I wanted. That also appears to be bug anyway making all but the required sheet invisible fixed that. Really clumsy solution and if I could make it all work from Excel would be lot better.

Thanks again for your help it is appreciated .

Peter

I do not have a Mac and can't comment on your Pdf problem, but on PC when trying to fit everything on one page, the following works for me:

Please Login or Register  to view this content.

Lewis

Thanks for the suggestion will try the addition of Zoom and let you know

No Luck i am afraid I even tried setting the worksheet back to the standard portrait 100% etc first but that did not help.

I'm out of silver bullets. Sorry it didn't work, but it was worth a try.

Lewis

Of course and thank u again I think it must be a bug in the Mac version not the first time.

Peter

Another thought came to me while I was sleeping.

Application.PrintCommuncation was introduced in Excel 2010 and is supposed to work in a manner similar to Application.ScreenUpdating to make communications with the Printer driver go faster. There seems to be a problem (or a feature) with Application.PrintCommunication that requires the value to be 'True' prior to the following commands:
a. .PrintArea
b. .LeftHeader
c. .CenterHeader
d. .RightHeader
e. .LeftFooter
f. .CenterFooter
g. .RightFooter

I have had problems with the command with for other settings as well. This command is also available for the Mac.

Code should work 100% of the time with the value of 'True', but performance is several seconds slower.
You might want to try both of the following, and perhaps one will work:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Good luck.

Lewis

Thanks I tried that and the command was not available in Mac maybe I had missed something will try again.

Thanks

Peter