Printing Issue

I am printing from an excel sheet that is "hidden behind the scenes" in a vb app (the end user never sees teh excel sheet-I just want to capture it's print capabilities). How can I let the user select the number of copies. I wasa thinking of an input box where they enter the number of copies but I need to relay that value to Excel or the printer. "Printer.Copies" didn't generate an error but didn't generate the correct result either. Thanks!:confused:

Quote:

---

"Printer.Copies" didn't generate an error but didn't generate the correct result either

---

Can you show us how you did it. I may only guess that your Printer object isn't set to your system default so it won't be able to change any of its properties.

I just used

Printer.Copies = intNumCopies

(which is the value returned by the InputBox).

Here is how to find and set Default Printer:

VB Code:

Option Explicit
 
Private 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
 
Private Sub Command1_Click()
'=====================================
Dim prt As Printer, prtName As String
 
    prtName = GetDefaultPrinter
    For Each prt In Printers
        If prt.DeviceName = prtName Then
            Set Printer = prt
            Exit For
        End If
    Next
    'Label1.Caption = "Default Printer: " & Printer.DeviceName
    Debug.Print "Default Printer: " & Printer.DeviceName
    'you may now proceed with your printing
    'Printer.Copies = intNumCopies
 
End Sub
 
Public Function GetDefaultPrinter() As String
'=============================================
Dim def$, di&, Pos&
 
    def = String$(128, 0)
    di = GetProfileString("WINDOWS", "DEVICE", "", def, 127)
    
    def = Left(def, di)
    Pos = InStr(1, def, ",")
    GetDefaultPrinter = Left(def, Pos - 1)
 
End Function

Although, Excel's Workbook object has a method PrintOut:
[vbcode}
wrbk.PrintOut , , 3, , Printer.DeviceName
[/Highlight]

Try it and may be it will hepl you.

Just Awesome! Thanks!