PDF help!

[Marlene.Sawhney]

Hello,

I have the below macro and am having a slight problem. The macro PDFs
the document from excel and saves it accurately. However, the document
PDFs portrait style instead of landscape. While it seems easy to
rotate the document, I have hundreds of PDFs that automatically go
into emails and then send automatically. Under page setup I have the
landscape button checked off.

In addition, if I just PDF the document manually then it PDFs
landscape.

Is it possible to rotate the document instead of doing it manually? Is
there something in the macro that is causing it to do this?

Thanks!


Sub Single_Sheet_PDF_Converter()

' PDF_convert Macro
' Converts and saves single worksheet as PDF - Atif Ahbab

Dim WS_Count As Integer
Dim I As Integer
Dim PSFileName As String, PDFFileName As String, DistillerCall As
String
Dim ReturnValue As Variant

' Changes the active printer to Adobe Acrobat
Application.ActivePrinter = "Adobe PDF on Ne00:"

'Define the path and filenames (can get the names from a cell, and
add the path & extension):
PSFileName = Range("C1").Value + ".PS"
PDFFileName = Range("C1").Value + ".PDF"

'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'The Sendkeys characters are the full path and filename, followed
by the "Enter" key.
' These are buffered until the "print to file" screen appears:
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
'Change Acrobat [] version number for your computer in next line
DistillerCall = "C:\Program Files\Adobe\Acrobat 7.0\Distillr
\Acrodist.exe" & _
" /n /q /o " & PDFFileName & " " & PSFileName

'Call the Acrobat Distiller to distill the PS file. ReturnValue is
zero
'if the application doesn't open correctly:
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName &
"failed."

'MsgBox ActiveWorkbook.Worksheets(I).Name

'
End Sub

 

[Brandt]

My two cents worth...

I would check the default Adobe Acrobat settings (in control panel |
Printers & Faxes) and try setting that to Landscape. Mine defaults to
Portrait and I suspect that the code is not looking at the setting in the
excel page setup, but rather just getting Adobe default setting. I am also
curious as to why the printtofile setting is set to true. It seems this
should not be necessary.

Good Luck!

Brandt

 

[Marlene.Sawhney]

My two cents worth...

I would check the default Adobe Acrobat settings (in control panel |
Printers & Faxes) and try setting that to Landscape. Mine defaults to
Portrait and I suspect that the code is not looking at the setting in the
excel page setup, but rather just getting Adobe default setting. I am also
curious as to why the printtofile setting is set to true. It seems this
should not be necessary.

Good Luck!

Brandt

Hello,

I have the below macro and am having a slight problem. The macro PDFs
the document from excel and saves it accurately. However, the document
PDFs portrait style instead of landscape. While it seems easy to
rotate the document, I have hundreds of PDFs that automatically go
into emails and then send automatically. Under page setup I have the
landscape button checked off.

In addition, if I just PDF the document manually then it PDFs
landscape.

Is it possible to rotate the document instead of doing it manually? Is
there something in the macro that is causing it to do this?

Sub Single_Sheet_PDF_Converter()

' PDF_convert Macro
' Converts and saves single worksheet as PDF - Atif Ahbab

Dim WS_Count As Integer
Dim I As Integer
Dim PSFileName As String, PDFFileName As String, DistillerCall As
String
Dim ReturnValue As Variant

' Changes the active printer to Adobe Acrobat
Application.ActivePrinter = "Adobe PDF on Ne00:"

'Define the path and filenames (can get the names from a cell, and
add the path & extension):
PSFileName = Range("C1").Value + ".PS"
PDFFileName = Range("C1").Value + ".PDF"

'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'The Sendkeys characters are the full path and filename, followed
by the "Enter" key.
' These are buffered until the "print to file" screen appears:
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
'Change Acrobat [] version number for your computer in next line
DistillerCall = "C:\Program Files\Adobe\Acrobat 7.0\Distillr
\Acrodist.exe" & _
" /n /q /o " & PDFFileName & " " & PSFileName

'Call the Acrobat Distiller to distill the PS file. ReturnValue is
zero
'if the application doesn't open correctly:
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName &
"failed."

'MsgBox ActiveWorkbook.Worksheets(I).Name

'
End Sub- Hide quoted text -

- Show quoted text -

I did check the settings and it doesn't change the way the document
PDFs. Does anyone have any suggestions or maybe a brand new macro?

Thanks!