• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Change the paper bin with VB

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
carruina
Asked:
carruina
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
carruinaAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
carruinaAuthor Commented:
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
 
PNJCommented:
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
 
carruinaAuthor Commented:
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
 
carruinaAuthor Commented:
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
 
DanRollinsCommented:
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
 
carruinaAuthor Commented:
Accept for the recomendation of the moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now