Solved: Excel 2007 ExportAsFixedFormat

[stanl]

Hi, I hope this hasn't already been covered. I downloaded and installed the SaveAsPDF plug-in for Office 2007. Wrote a routine to test ExportAsFixedFormat to place workbook, worksheet, or range into PDF format.

Worked quickly and output was good. However, even though I set my page setup to Landscape, the PDF document was portrait. There are no parameters in the Export function for page layout, and I Googled several posts that referred to it as a problem. Maybe someone here has run across a workaround, Stan:doh:

[anandbohra]

hi stanl

the SaveAsPDF plug-in for Office 2007 exports data in PDF & XPS based on your current document settings.

so first do the page setup in your active file then save it as PDF
it will do the needful & tested at my end.

if u want u can attach the file & i will attach its PDF in the way u prescribe (landscape or Portrait)

[stanl]

so first do the page setup in your active file then save it as PDF


I did that, it still comes out portrait, I don't think the saveaspdf add-on has anything to do with print preview. I created my code based on this example from Ron de Bruin . File is attached in zip


Sub RDB_PDF_ActiveWorkbook()
Dim FilenameStr As String

If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

FilenameStr = Application.DefaultFilePath & "\" & _
Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "You can find the PDF file here : " & FilenameStr
Else
MsgBox "PDF add-in Not Installed"
End If
End Sub

[anandbohra]

hi stanl

the process which i am following is as mentioned below
1 - open the document or spreadsheet in office 2007 (word 07 or excel 07)
2 - then go to office menu (office logo at top left corner or press Alt + F)
3 - Under print option go to print preview
4 - Under print setup set your page to desired one A4, Letter, Legal
5 - There also change page orientation to landscape or portrait
6 - close print preview.
7 - Press Alt + F then under Save as - PDF or XPS
8 - Press publish
so what ever u had set in your excel or word file the same u get in PDF
i had attached the pdf file both in Landscape & Portrait the same u had send me in excel

[stanl]

I think one of us has missed the point. I know you can do it manually; what I am trying to do is use the VBA code. Stan

[anandbohra]

it is irrespective to do manually or through VBA or recording the PDF will be in same format as current printout setting

so even before running the code either manually do page setup or set the page through VBA code

[stanl]

so even before running the code either manually do page setup or set the page through VBA code

That is what I did, the workbook has the page setup as landscape and the code doesn't work, although it does work manually. So what am I missing? Stan

[stanl]

Very strange. I want this ccode to work external to each workbook. The original file I tried it on was an .xls saved as .xlsx - that one output PDF as Portrait no matter what I did. I just ran my script again to create the .xlsx file from scratch, set the page orientation to landscape and the pdf output was landscape. Go figure. I'll mark as solved and thank you for your patience. Stan:friends:

[anandbohra]

ok