Solved

Change the paper bin with VB

Posted on 2002-07-10
9
409 Views
Last Modified: 2012-05-04
I have an application that open an Excel Sheet, write some
data and print this Sheet.

Now I need change the printer and the paper bin to send this file to diferents printers.

I try with this code but don't work


'Search the printer pImpresora
For Each Auxprinter In Printers
    If Auxprinter.DeviceName = Trim(pImpresora) Then
        PrintEnc = True
        Set Printer = Auxprinter
        Exit For
    End If

If PrintEnc Then
    Printer.PaperBin = pBandeja
    xlSheet.PrintOut , , pCopias, , Printer.DeviceName
Else 'Send the file to the default printer if pImpresora doen't exists.
    xlSheet.PrintOut , , pCopias, , Printer.DeviceName
   
End If

I can send the file to diferents printers (pImpresora) but I can't change the paperbin.

Thanks in advance for your colaborations
0
Comment
Question by:carruina
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7143778
0
 
LVL 5

Expert Comment

by:rpai
ID: 7143789
0
 
LVL 3

Author Comment

by:carruina
ID: 7143853
angelIII :
That don´t solve my problem because when I print the document with excel and It don't take the correct paperbin
 xlSheet.PrintOut , , pCopias, , Printer.DeviceName
Take the default paperbin of the printer.
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 3

Author Comment

by:carruina
ID: 7143873
rpai:

This example is good but Doesn´t work with Excel 97:

I can't set a printer object to a Excel Sheet

Thanks
0
 
LVL 3

Accepted Solution

by:
PNJ earned 500 total points
ID: 7143992
Printer.PaperBin does work - I do it all the time, so I think something else may be wrong here. It depends on the printer driver AND the operating system to determine which bin to use. I've given you a little routine that checks to see if the chosen printer can handle the requested bin. Then you'll know that you're at least chosing a bin that will work in the first place.

Private Declare Function OpenPrinter Lib "winspool.drv" Alias _
      "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, _
      ByVal pDefault As Long) As Long
     
Private Declare Function ClosePrinter Lib "winspool.drv" ( _
      ByVal hPrinter As Long) As Long
     
Private Declare Function DeviceCapabilities Lib "winspool.drv" _
      Alias "DeviceCapabilitiesA" (ByVal lpDeviceName As String, _
      ByVal lpPort As String, ByVal iIndex As Long, lpOutput As Any, _
      ByVal dev As Long) As Long
   
Const DC_BINS = 6
Const DC_BINNAMES = 12

Function CheckPrinterBin(Prn As Printer, BinNo As Integer) As Boolean

   ' Check if the specified printer can select the requested bin
   
   Dim hPrinter As Long
   Dim dwbins As Long
   Dim i As Integer
   Dim NumBin() As Integer
   
   CheckPrinterBin = False
   
   ' Open the printer to get a handle
   If OpenPrinter(Prn.DeviceName, hPrinter, 0) <> 0 Then
   
      ' Tell me how many bins you've got
      dwbins = DeviceCapabilities(Prn.DeviceName, Prn.Port, DC_BINS, ByVal vbNullString, 0)
     
      ' Create the list for this many
      ReDim NumBin(1 To dwbins)
     
      ' ...and get the list now we know how many there are
      dwbins = DeviceCapabilities(Prn.DeviceName, Prn.Port, DC_BINS, NumBin(1), 0)
     
      ' Look for the bin number
      For i = 1 To UBound(NumBin, 1)
         If NumBin(i) = BinNo Then CheckPrinterBin = True
      Next i
     
      ' Close the printer whatever
      Call ClosePrinter(hPrinter)
     
   Else   ' OpenPrinter failed, so cannot retrieve information
   
      CheckPrinterBin = False
     
   End If
   
End Function

If that doesn't work then maybe Excel is doing something weird.
0
 
LVL 3

Author Comment

by:carruina
ID: 7145373
I tested your function and works fine

The printer has this possibilities:
1,2,3,14,4,6,7

I always try to use paperbin 1 and 2

I don't have any problem to change the paperbin and this works fine if I print from visual basic.

The problem appears when I want print using Excel from Visual Basic.

I change the paperbin but when send the file with this command line "xlSheet.PrintOut , , pCopias, , Printer.DeviceName" Excel gets the system configuration of this printer.

I need change the printer configuration with VB but permanently.

For example when you use Printer Object the changes, that you do it, only are "visible" in the application VB. If you close the application and see in the printers control panel the configuration are the previosly. And this is the configuration of the printer.
0
 
LVL 3

Author Comment

by:carruina
ID: 7145391
I tested your function and works fine

The printer has this possibilities:
1,2,3,14,4,6,7

I always try to use paperbin 1 and 2

I don't have any problem to change the paperbin and this works fine if I print from visual basic.

The problem appears when I want print using Excel from Visual Basic.

I change the paperbin but when send the file with this command line "xlSheet.PrintOut , , pCopias, , Printer.DeviceName" Excel gets the system configuration of this printer.

I need change the printer configuration with VB but permanently.

For example when you use Printer Object the changes, that you do it, only are "visible" in the application VB. If you close the application and see in the printers control panel the configuration are the previosly. And this is the configuration of the printer.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8002271
Hi carruina,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept PNJ's comment(s) as an answer.

carruina, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 3

Author Comment

by:carruina
ID: 8006807
Accept for the recomendation of the moderator
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

803 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