PDA

View Full Version : VBA excel 2011 save as PDF



cyrilbrd
09-19-2012, 01:19 AM
As posted in OZGRID (can't post the link)


Used the following:

Sub SavePDFMacro()

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



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.

mikerickson
09-26-2012, 09:32 PM
I'm going to move this to the general Excel sub forum, hoping that someone can help.

cyrilbrd
09-26-2012, 09:35 PM
Noted,
Thanks for the push.

mancubus
09-27-2012, 02:45 AM
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.

arronlee
07-16-2013, 01:46 AM
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.

arronlee
08-19-2013, 07:41 PM
I have tried your code, it works great. Thanks again.

fr335tat3
07-06-2017, 05:50 PM
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

Logit
07-06-2017, 07:02 PM
.
https://stackoverflow.com/questions/15084462/wrong-pdf-output-size-with-exportasfixedformat-in-excel-vba

http://blogs.adobe.com/acrolaw/2015/02/creating-a-pdf-at-the-right-output-size-and-dimensions/

mancubus
07-06-2017, 11:20 PM
this thread is 5 years old fr335 :cool:

Logit
07-07-2017, 03:21 AM
.
The living dead ?
:banghead: