Consulting

Results 1 to 10 of 10

Thread: VBA excel 2011 save as PDF

  1. #1

    VBA excel 2011 save as PDF

    As posted in OZGRID (can't post the link)


    Used the following:

    [VBA]Sub SavePDFMacro()

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "Macintosh HD:Users:Mach7esktop:" & Range("AA1").Value, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End Sub

    [/VBA]

    The set print area should be saved as a PDF file onto the desktop using as file name the value found in Cell AA1.
    The code is doing just that. No issue here.

    I would like to adjust the paper size and the margin as to maximize the appearance.
    Right now I get a pdf that is either a A4 or a 8.5 by 11.
    Any suggestion to force the pdf to save as legal that is 8.5 by 13 (215.9 by 330.91 mm). Note Excel is already using this size as default, however the created PDF does not.

    Thanks for your suggestions.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I'm going to move this to the general Excel sub forum, hoping that someone can help.

  3. #3
    Noted,
    Thanks for the push.

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    Quote Originally Posted by cyrilbrd
    As posted in OZGRID (can't post the link)
    http://www.ozgrid.com/forum/showthread.php?t=169852

    can post links if post count exceeds 5.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    Using code is too complicated for me. As for myself, I used to convert excel 2011 to PDF files with the help of another PDF conversion SDK. It is just one of many but I do appreciate its simple way of processing. Even though I only tried its free trial package to convert PDF files and didn′t check the cost and licensing conditions, it works great for me. Share with you. And I will try your CODE later. I hope you success. Good luck.
    Best Regards,
    Arron

    I am testing about image resizers to resize images, any ideas?


    Next Tomorrow is Another Day.

  6. #6
    I have tried your code, it works great. Thanks again.

  7. #7

    AppleScript to set page size

    Quote Originally Posted by cyrilbrd View Post
    As posted in OZGRID (can't post the link)


    I would like to adjust the paper size and the margin as to maximize the appearance.
    Right now I get a pdf that is either a A4 or a 8.5 by 11.
    Any suggestion to force the pdf to save as legal that is 8.5 by 13 (215.9 by 330.91 mm). Note Excel is already using this size as default, however the created PDF does not.

    Thanks for your suggestions.

    I beat my head on the wall for a while on this and figured out some AppleScript to set page size just before printing. Excel 2011 has some bug where it won't remember page size when saving the file. This is my work around. Use the VBA macscript command with the following script.

    tell application "Microsoft Excel"
    activate
    end tell


    tell application "System Events"
    delay 0.5
    --keystroke "a" using command down
    tell application process "Microsoft Excel"
    click menu item "Page Setup..." of menu "File" of menu bar item "File" of menu bar 1
    delay 0.5
    keystroke "o" using command down --This clicks the Option... button
    delay 0.5
    click pop up button 3 of window "Page Setup" --This clicks the Paper Size: drop down. button 2 clicks the Format For: drop down
    delay 0.5
    keystroke "4" --Use the first couple of letters of your desired page size. for me it was 4" x 2-1/2" tag
    keystroke return
    delay 0.5
    keystroke return
    delay 0.5
    keystroke return
    delay 0.5
    end tell
    end tell

  8. #8

  9. #9
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    this thread is 5 years old fr335
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  10. #10
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    The living dead ?

Posting Permissions

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