Set excel export print format to landscape when creating from a lotusscript agent

Posted on 2009-02-18
Last Modified: 2013-11-16
I am generating a report for the users from a browser based system, it is required in both word and excel format.

We do not have Office on the servers so maniplutaing the formating is fairly restrictive.

I have go the report to print in word as landscape by using the following in the lotuscript agent;

      Print {Content-type: application/msword}
      Print {Content-Disposition: Attachment; filename="High Level Capital Summary.doc"}
      Print {<HTML>}
      Print |            <style>|
      Print |            p.MsoHeader, li.MsoHeader, div.MsoHeader      {|
      Print |                  margin                                          :      0cm;      |
      Print |                  margin-bottom                        :      .0001pt;      |
      Print |                  mso-pagination                        :      widow-orphan;      |
      Print |                  tab-stops                                    :      center 207.65pt right 415.3pt;      |
      Print |                  font-size                                    :      9.0pt;      |
      Print |                  font-family                                    :      "Times New Roman";      |
      Print |                  mso-fareast-font-family      :      "Times New Roman";      |
      Print |            }      |
      Print |                  @page  Section1{|
      Print |                           size                                          :      27.94cm 21.59cm; |
      Print |                         mso-page-orientation:      landscape; |
      Print |                           margin                                    :      2.54cm  3.17cm 3.17cm 2.54cm ; |
      Print |                           mso-header-margin      :      .5in; |
      Print |                           mso-footer-margin            :      .5in; mso-paper-source:0;|
      Print |                         mso-footer-data              :      "Page &P"; |
      Print |                  }|
      Print |                   div.Section1 {|
      Print |                           page                                          :      Section1;|
      Print |                  }|
      Print |            </style>|
      Print |      </HEAD>|
      Print {<div class=Section1>}

//All the output details
                     Print {</div>}

but this does not work with excel, just defaults to portrait.

Thanks in advance for any help / suggestions.
Question by:Nologo666
    LVL 22

    Accepted Solution

    Type DIVT
         quot As Long
         remain As Long
    End Type
         pDatatype As Long
         pDevmode As Long
         DesiredAccess As Long
    End Type
    Private Type PRINTER_INFO_2
         pServerName As Long
         pPrinterName As Long
         pShareName As Long
         pPortName As Long
         pDriverName As Long
         pComment As Long
         pLocation As Long
         pDevmode As Long 
         pSepFile As Long
         pPrintProcessor As Long
         pDatatype As Long
         pParameters As Long
         pSecurityDescriptor As Long 
         Attributes As Long
         Priority As Long
         DefaultPriority As Long
         StartTime As Long
         UntilTime As Long
         Status As Long
         cJobs As Long
         AveragePPM As Long
    End Type
    Type DEVMODE
         dmDeviceName As String * 32
         dmSpecVersion As Integer
         dmDriverVersion As Integer
         dmSize As Integer
         dmDriverExtra As Integer
         dmFields As Long
         dmOrientation As Integer
         dmPaperSize As Integer
         dmPaperLength As Integer
         dmPaperWidth As Integer
         dmScale As Integer
         dmCopies As Integer
         dmDefaultSource As Integer
         dmPrintQuality As Integer
         dmColor As Integer
         dmDuplex As Integer
         dmYResolution As Integer
         dmTTOption As Integer
         dmCollate As Integer
         dmFormName As String * 32
         dmUnusedPadding As Integer
         dmBitsPerPel As Integer
         dmPelsWidth As Long
         dmPelsHeight As Long
         dmDisplayFlags As Long
         dmDisplayFrequency As Long
         dmICMMethod As Long
         dmICMIntent As Long
         dmMediaType As Long
         dmDitherType As Long
         dmReserved1 As Long
         dmReserved2 As Long
    End Type
    Declare Function ClosePrinter Lib "winspool.drv" (Byval hPrinter As Long) As Long
    Declare Function DocumentProperties Lib "winspool.drv" Alias "DocumentPropertiesA" (Byval hwnd As Long, _
    Byval hPrinter As Long, Byval pDeviceName As String, pDevModeOutput As Long, pDevModeInput As Long, _
    Byval fMode As Long) As Long
    Declare Function GetPrinter Lib "winspool.drv" Alias "GetPrinterA" (Byval hPrinter As Long, Byval Level As Long, _
    pPrinter As Long, Byval cbBuf As Long, pcbNeeded As Long) As Long
    Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (Byval pPrinterName As String, phPrinter As Long, _
    pDefault As PRINTER_DEFAULTS) As Long
    Declare Function SetPrinter Lib "winspool.drv" Alias "SetPrinterA" (Byval hPrinter As Long,_
    Byval Level As Long, pPrinter As Long, Byval commandl As Long) As Long
    Declare Sub CopyMemory Lib "Kernel32" Alias "RtlMoveMemory" (pDest As DEVMODE, pSource As Long, Byval cbLength As Long)
    Declare Sub CopyMemoryBack Lib "Kernel32" Alias "RtlMoveMemory" (pDest As Long, pSource As DEVMODE, Byval cbLength As Long)
    Declare Sub CopyMemoryPI Lib "Kernel32" Alias "RtlMoveMemory" (pDest As PRINTER_INFO_2, pSource As Long, Byval cbLength As Long)
    Declare Sub CopyMemoryBackPI Lib "Kernel32" Alias "RtlMoveMemory" (pDest As Long, pSource As PRINTER_INFO_2, Byval cbLength As Long)
    Declare Function labs Lib "msvcrt.dll" Alias "labs" (param As Long) As Long
    Declare Function ldiv Lib "msvcrt.dll" (Byval q As Long, divisor As Long) As Long
    Function SetPrinterOrientation(Byval sPrinterName As String,Byval orientation As Integer) As Long     
         Const DM_IN_BUFFER = 8
         Const DM_OUT_BUFFER = 2
         Const PRINTER_ACCESS_USE = &H8
         Dim hPrinter As Long
         Dim pd As PRINTER_DEFAULTS
         Dim pinfo As PRINTER_INFO_2
         Dim dm As DEVMODE
         Dim dm2 As DEVMODE
         Dim yDevModeData() As Long
         Dim yPInfoMemory() As Long
         Dim nBytesNeeded As Long
         Dim nRet As Long, nJunk As Long
         Dim q As Long
         On Error Goto cleanup
     'Open printer with all access to be able to modify settings
         pd.DesiredAccess = PRINTER_ALL_ACCESS
         nRet = OpenPrinter(sPrinterName, hPrinter, pd)
         nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
         If (nRet < 0) Then
              Msgbox "Cannot get the size of the DEVMODE structure."
              Goto cleanup
         End If
         Redim yDevModeData(nRet + 100) As Long
         nRet = DocumentProperties(0, hPrinter, sPrinterName, yDevModeData(0), 0, DM_OUT_BUFFER)
         If (nRet < 0) Then
              Msgbox "Cannot get the DEVMODE structure."
              Goto cleanup
         End If
         Call CopyMemory(dm, yDevModeData(0), Len(dm))
         dm.dmOrientation = orientation
         Call CopyMemoryBack(yDevModeData(0), dm, Len(dm))
         nRet = DocumentProperties(0, hPrinter, sPrinterName, yDevModeData(0), yDevModeData(0), DM_IN_BUFFER Or DM_OUT_BUFFER)
         If (nRet < 0) Then          
    	     Msgbox "Unable to set some settings to this printer."
              Goto cleanup
         End If
         Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
         If (nBytesNeeded = 0) Then Goto cleanup
         Redim yPInfoMemory(nBytesNeeded + 100) As Long
         nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
         If (nRet = 0) Then
              Msgbox "Unable to get shared printer settings."
              Goto cleanup
         End If
         Call CopyMemoryPI(pinfo, yPInfoMemory(0), Len(pinfo))
         pinfo.pDevmode = labs(yDevModeData(0))
         pinfo.pSecurityDescriptor = 0
         Call CopyMemoryBackPI(yPInfoMemory(0), pinfo, Len(pinfo))
         nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
         If (nRet = 0) Then
              Msgbox "Unable to set shared printer settings."
         End If
         SetPrinterOrientation = nRet
         If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)
    End Function
    Sub Click(Source As Button)
    	'sets the current default printer to landscape mode     Dim result As Variant
         Dim printerName As String
         Dim nPos As String
    	'Get the printer name
         printerName = String(128, 0)
         result = GetProfileString("WINDOWS", "DEVICE", "", printerName, 127)
         nPos = Instr(printerName, ",")
         printerName = Left(printerName, nPos - 1)
    	'Set the default printer to landscape (printer name, landscape orientation)
         result = SetPrinterOrientation(printerName, 2)
    End Sub

    Open in new window


    Author Comment

    Thanks for that,

    Although I am not sure it will do the job. Will this work for a Browser based system? as It appears to be more for a client system.
    I might be missing something through.

    I was hoping I could do it along similar lines to the way I have done it with the word output.
    LVL 22

    Expert Comment

    If you have LotusScript agent, as you said you have, then simply add the code there...

    Author Comment

    Sorry I have been off work, will test your solution in the next couple of days.
    LVL 22

    Expert Comment

    I think my comment should be accepted here.
    It simply is the solution to the problem.
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Sorry for barging in at closing time, but here are solutions that seem more like what Nologo wanted:

    I assume the line with @page is somehow not correct..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Outlook Free & Paid Tools
    This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now