?
Solved

Printing an Excel Worksheet from VB

Posted on 2003-02-20
8
Medium Priority
?
377 Views
Last Modified: 2012-06-27
Hi,
This is the problem I am facing.
I have created a worksheet, containing about 10 coluums ans rows. The data for the worksheet is input from a DB Recordset.
Now, I need to print the Active Worksheet to a Printer, in Landscape mode.

I do not have the option to make it the default printer.

I am printing the worksheet using :

CWorkbook.ActiveSheet.PrintOut ActivePrinter:="\\PrintServer\HPLaserJ.2"

Is there any way to print this sheet in Landscape ( by setting some properties in the Worksheet), without making the printer the default and setting the orientation of the printer object ?

I would appretiate any help in this matter.
Thanks,
Kenny.

But i cannot change the orientation.
0
Comment
Question by:karnso77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:AmericanDogma
ID: 7990556
The code to change orientation is:

Printing Landscape
Printer.Orientation = 2

OR

Printing Portrait
Printer.Orientation = 1
0
 
LVL 3

Expert Comment

by:AmericanDogma
ID: 7990570
Here is an article detailing the printer object more indepth

http://www.freevbcode.com/ShowCode.Asp?ID=3646
0
 
LVL 27

Expert Comment

by:Dabas
ID: 7991087
A little known fact that helps solve this problem easily is that you are allowed to have the same printer twice, (with different default settings)

I suggest you add a new printer (name it HPLaserJ.2 Landscape) and set it to Landscape.

Use this printer in your code

Dabas
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 3

Expert Comment

by:VBtom
ID: 7992709
Use
CWorkbook.ActiveSheet.pagesetup.orientation = xlLandscape
prior to the .Printout
0
 

Accepted Solution

by:
karnso77 earned 0 total points
ID: 8012506
Thanks a Lot VBtom, AmericaDogma and Dabas.
I have found a way to alter the orientation and the properties of a printer.

That is not the problem now.
Everything seems to work fine when I run the Exe from the CMD Prompt.
Once I try it through WINAT, the Mess Starts.
I cannot alter the PageSetup properties because Excel cannot find a printer. This is confusing because there is a whole slew of printers installed on the machine.

I granted the USERID "logon as service" access to kick it off through WINAT, but cannot get this printers problem resolved.

I know I will have to post this As a Seperate Question , but thanks for the input.

Kenny.

(The code to alter the printer properties is I use is (It is a comination of 2 articles I read somewhere )).
Option Explicit
   
   Public Const HWND_BROADCAST As Long = &HFFFF&
   Public Const WM_WININICHANGE = &H1A
   Public Enum PrinterOrientationConstants
   OrientPortrait = 1
   OrientLandscape = 2
    End Enum

   Public Type PRINTER_DEFAULTS

       pDatatype As Long
       pDevmode As Long
       DesiredAccess As Long
   End Type

   Public 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       ' Pointer to DEVMODE
       pSepFile As Long
       pPrintProcessor As Long
       pDatatype As Long
       pParameters As Long
       pSecurityDescriptor As Long  ' Pointer to SECURITY_DESCRIPTOR
       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

   Public 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

   Public Const DM_DUPLEX = &H1000&
   Public Const DM_ORIENTATION As Long = &H1
   Public Const DM_IN_BUFFER = 8

   Public Const DM_OUT_BUFFER = 2
   Public Const PRINTER_ACCESS_ADMINISTER = &H4
   Public Const PRINTER_ACCESS_USE = &H8
   Public Const STANDARD_RIGHTS_REQUIRED = &HF0000
   Public Const PRINTER_ALL_ACCESS = (STANDARD_RIGHTS_REQUIRED Or _
             PRINTER_ACCESS_ADMINISTER Or PRINTER_ACCESS_USE)

   Public Declare Function ClosePrinter Lib "winspool.drv" _
    (ByVal hPrinter As Long) As Long
   Public Declare Function DocumentProperties Lib "winspool.drv" _
     Alias "DocumentPropertiesA" (ByVal hwnd As Long, _
     ByVal hPrinter As Long, ByVal pDeviceName As String, _
     ByVal pDevModeOutput As Long, ByVal pDevModeInput As Long, _
     ByVal fMode As Long) As Long
   Public Declare Function GetPrinter Lib "winspool.drv" Alias _
     "GetPrinterA" (ByVal hPrinter As Long, ByVal level As Long, _
     pPrinter As Byte, ByVal cbBuf As Long, pcbNeeded As Long) As Long
   Public Declare Function OpenPrinter Lib "winspool.drv" Alias _
     "OpenPrinterA" (ByVal pPrintername As String, phPrinter As Long, _
     pDefault As PRINTER_DEFAULTS) As Long
   Public Declare Function SetPrinter Lib "winspool.drv" Alias _
     "SetPrinterA" (ByVal hPrinter As Long, ByVal level As Long, _
     pPrinter As Byte, ByVal Command As Long) As Long
   
   Public Declare Function GetProfileString Lib "kernel32" Alias "GetProfileStringA" (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long) As Long
Public Declare Function WriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal lpszSection As String, ByVal lpszKeyName As String, ByVal lpszString As String) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lparam As String) As Long
Public Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Any) As Long
Public Declare Function GetLastError Lib "kernel32" () As Long
   
   Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (pDest As Any, pSource As Any, ByVal cbLength As Long)
   
 
   ' ==================================================================
   ' SetPrinterDuplex
   '
   '  Programmatically set the Duplex flag for the specified printer
   '  driver's default properties.
   '
   '  Returns: True on success, False on error.
   '
   '  Parameters:
   '    sPrinterName - The name of the printer to be used.
   '
   '    nDuplexSetting - One of the following standard settings:
   '       1 = None
   '       2 = Duplex on long edge (book)
   '       3 = Duplex on short edge (legal)
   '
   ' ==================================================================
   Public Function SetPrinterDuplex(ByVal sPrinterName As String, ByVal nDuplexSetting As Long) As Long

      Dim hPrinter As Long
      Dim pd As PRINTER_DEFAULTS
      Dim pInfo As PRINTER_INFO_2
      Dim dm As DEVMODE
   
      Dim yDevModeData() As Byte
      Dim yPInfoMemory() As Byte
      Dim nBytesNeeded As Long
      Dim nRet As Long, nJunk As Long
   
      On Error GoTo cleanup
   
      If (nDuplexSetting < 1) Or (nDuplexSetting > 3) Then
         SetPrinterDuplex = False
         Exit Function
      End If
     
      pd.DesiredAccess = PRINTER_ALL_ACCESS
      nRet = OpenPrinter(sPrinterName, hPrinter, pd)
      If (nRet = 0) Or (hPrinter = 0) Then
         If Err.LastDllError = 5 Then
            SetPrinterDuplex = nRet
         Else
            SetPrinterDuplex = nRet
         End If
         Exit Function
      End If
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
      If (nRet < 0) Then
         SetPrinterDuplex = False
         GoTo cleanup
      End If
   
      ReDim yDevModeData(nRet + 100) As Byte
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
                  VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
      If (nRet < 0) Then
         SetPrinterDuplex = False
         GoTo cleanup
      End If
   
      Call CopyMemory(dm, yDevModeData(0), Len(dm))
   
      If Not CBool(dm.dmFields And DM_DUPLEX) Then
            SetPrinterDuplex = False
            GoTo cleanup
      End If
   
      dm.dmDuplex = nDuplexSetting
      Call CopyMemory(yDevModeData(0), dm, Len(dm))
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
        VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
        DM_IN_BUFFER Or DM_OUT_BUFFER)

      If (nRet < 0) Then
        SetPrinterDuplex = False
        GoTo cleanup
      End If
   
      Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
      If (nBytesNeeded = 0) Then GoTo cleanup
   
      ReDim yPInfoMemory(nBytesNeeded + 100) As Byte

      nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
      If (nRet = 0) Then
         SetPrinterDuplex = False
         GoTo cleanup
      End If
   
      Call CopyMemory(pInfo, yPInfoMemory(0), Len(pInfo))
      pInfo.pDevmode = VarPtr(yDevModeData(0))
      pInfo.pSecurityDescriptor = 0
      Call CopyMemory(yPInfoMemory(0), pInfo, Len(pInfo))
   
      nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
      If (nRet = 0) Then
         SetPrinterDuplex = False
         GoTo cleanup
      End If
     
      'Able to Set Printer Properties successfully
      SetPrinterDuplex = True
cleanup:
      If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)
End Function

' ====================================================================================
' checkPrinter - Checks for the Existance of the Printer in the windows Registry
'                It is called before the SetPrinterDuplex function to avoid any
'                unncessary Errors in the Program
'
' Parameters   - pPrintername - Name of the Printer to Search for
' Return Value - NULL String if the Printer is not found. Name of the Printer if the
'                 printer is found
' ====================================================================================
Public Function checkPrinter(pPrintername As String) As String
On Error GoTo checkPrinterError
    Dim r As Long
    Dim DriverName As String
    Dim PrinterPort As String
    Dim Buffer As String
    Dim RetVal As Boolean
   
    checkPrinter = vbNullString
    'Get the list of available printers from WIN.INI
    Buffer = Space(1024)
    r = GetProfileString("PrinterPorts", pPrintername, "", _
       Buffer, Len(Buffer))

    'Parse the driver name and port name out of the buffer
    GetDriverAndPort Buffer, DriverName, PrinterPort

    If DriverName <> "" And PrinterPort <> "" Then
            checkPrinter = pPrintername
    Else
        GoTo checkPrinterError
    End If
    Exit Function
checkPrinterError:
    'The specified Printer is not Defined in the Windows Registry. Check the Printer Name
    checkPrinter = vbNullString
End Function
Private Sub GetDriverAndPort(ByVal Buffer As String, DriverName As String, PrinterPort As String)
    Dim iDriver As Integer
    Dim iPort As Integer
    DriverName = ""
    PrinterPort = ""

    'The driver name is first in the string terminated by a comma
    iDriver = InStr(Buffer, ",")
    If iDriver > 0 Then

        'Strip out the driver name
        DriverName = Left(Buffer, iDriver - 1)

        'The port name is the second entry after the driver name
        'separated by commas.
        iPort = InStr(iDriver + 1, Buffer, ",")

        If iPort > 0 Then
            'Strip out the port name
            PrinterPort = Mid(Buffer, iDriver + 1, _
            iPort - iDriver - 1)
        End If
    End If
End Sub
Public Function SetPrinterOrientation(ByVal sPrinterName As String, ByVal nOrientSetting As PrinterOrientationConstants) As Boolean

      Dim hPrinter As Long
      Dim pd As PRINTER_DEFAULTS
      Dim pInfo As PRINTER_INFO_2
      Dim dm As DEVMODE
   
      Dim yDevModeData() As Byte
      Dim yPInfoMemory() As Byte
      Dim nBytesNeeded As Long
      Dim nRet As Long, nJunk As Long
      Dim rResetPrinterOrientation As Boolean
     
      On Error GoTo cleanup
   
     
      pd.DesiredAccess = PRINTER_ALL_ACCESS
      nRet = OpenPrinter(sPrinterName, hPrinter, pd)
      If (nRet = 0) Or (hPrinter = 0) Then
         If Err.LastDllError = 5 Then
            SetPrinterOrientation = False
         Else
            SetPrinterOrientation = False
         End If
         Exit Function
      End If
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
      If (nRet < 0) Then
         SetPrinterOrientation = False
         GoTo cleanup
      End If
   
      ReDim yDevModeData(nRet + 100) As Byte
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
                  VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
      If (nRet < 0) Then
         SetPrinterOrientation = False
         GoTo cleanup
      End If
   
      Call CopyMemory(dm, yDevModeData(0), Len(dm))
     
      If Not dm.dmOrientation = nOrientSetting Then
            rResetPrinterOrientation = True
      End If
       
      If Not CBool(dm.dmFields And DM_ORIENTATION) Then
            SetPrinterOrientation = False
            GoTo cleanup
      End If
   
      dm.dmOrientation = nOrientSetting
     
      Call CopyMemory(yDevModeData(0), dm, Len(dm))
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
        VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
        DM_IN_BUFFER Or DM_OUT_BUFFER)

      If (nRet < 0) Then
        SetPrinterOrientation = False
        GoTo cleanup
      End If
   
      Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
      If (nBytesNeeded = 0) Then GoTo cleanup
   
      ReDim yPInfoMemory(nBytesNeeded + 100) As Byte

      nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
      If (nRet = 0) Then
         SetPrinterOrientation = False
         GoTo cleanup
      End If
   
      Call CopyMemory(pInfo, yPInfoMemory(0), Len(pInfo))
      pInfo.pDevmode = VarPtr(yDevModeData(0))
      pInfo.pSecurityDescriptor = 0
      Call CopyMemory(yPInfoMemory(0), pInfo, Len(pInfo))
   
      nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
      If (nRet = 0) Then
         SetPrinterOrientation = False
         GoTo cleanup
      End If
     
      'Able to Set Printer Properties successfully
      SetPrinterOrientation = rResetPrinterOrientation
     
cleanup:
      If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)
End Function


0
 

Expert Comment

by:CleanupPing
ID: 9447610
karnso77:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 9447887
Suggest PAQ/Refund.
Asker has supplied his own answer and posted it

Dabas
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

801 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