Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change the paper bin with VB

Posted on 2002-07-10
9
Medium Priority
?
426 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 143

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

879 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