+ Reply to Thread
Results 1 to 12 of 12

Export a Selection as a PDF

  1. #1
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    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

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    820

    Re: Export a Selection as a PDF

    Hi,
    you are calling a procedure "LastCell", where probably LRow and LCol are set. If those two are not set, no correct range will be set in "PrintA"


    Cheers
    Erwin
    Last edited by Eastw00d; 04-18-2020 at 04:18 AM. Reason: typo
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

    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.

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    820

    Re: Export a Selection as a PDF

    The messagebox says nothing about the range. That is gives an output is because mypath and myoutput is correct set up.
    Cheers
    Erwin

  5. #5
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

    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

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Export a Selection as a PDF

    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

  7. #7
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

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

  8. #8
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

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

  9. #9
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Export a Selection as a PDF

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

    Lewis

  10. #10
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

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

    Peter

  11. #11
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Export a Selection as a PDF

    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

  12. #12
    Registered User
    Join Date
    09-05-2018
    Location
    Vaill, CO
    MS-Off Ver
    Mac2011
    Posts
    10

    Re: Export a Selection as a PDF

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

    Thanks

    Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Possible vba export specific sheets to pdf with range selection
    By zrs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2019, 01:52 PM
  2. Automatically export a selection of individual worksheet into a master sheet
    By louhazosc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2018, 11:45 AM
  3. [SOLVED] PDF export from selection under name from specified cells
    By nosense in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2015, 04:31 AM
  4. Replies: 0
    Last Post: 02-06-2015, 04:50 PM
  5. Export Selection as MS-DOS CSV File with 12 commas
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2012, 04:42 AM
  6. Excel 2007 : Export Selection As A Windows Dos Bat File
    By vivekhalder in forum Excel General
    Replies: 8
    Last Post: 09-28-2011, 02:39 PM
  7. Macro or method for identical data selection/export
    By boozer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2006, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1