Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PDF help!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PDF help!
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PDF help!
On Dec 10, 12:22 pm, Brandt wrote:
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 " wrote: 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- 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|