Printing VBA required.

[Craig1]

Hi guys,

I have a little issue I need to resolve, I have searched the forum but not found exactly what I need. Any help is appreciated.
Some of my work colleagues really struggle to even set up printer settings via some code I set up with the help of you guys. After rolling it out a couple of weeks ago quite a few are struggling with it so I need the code to do even more.

The code I use at the moment is on a command button and it works perfect if you choose the correct settings when it brings the "printer settings" up.

Private Sub CommandButton1_Click()
Dim bPrinted As Boolean
Dim LR As Long
'Print permit
Calculate

Unprotect Password:="password"

With Sheets("Electronic Permits")
LR = .Range("B" & Rows.Count).End(xlUp).Row
.Range("E" & LR).Value = Now
.Range("D" & LR).Value = Environ("username")
.Range("A" & LR).Copy

Application.ScreenUpdating = False
sheetname = .Range("C" & LR).Value
Sheets(sheetname).Activate
Sheets(sheetname).Visible = True
ActiveSheet.Range("D5").Select
ActiveSheet.Range("D5").PasteSpecial
Application.Dialogs(xlDialogPrinterSetup).Show
ActiveSheet.PageSetup.PrintArea = "$A$1:$AK$96"
ActiveWindow.SelectedSheets.PrintOut , copies:=1, collate:=True
Sheets(sheetname).Visible = xlSheetVeryHidden
Sheets("Electronic Permits").Activate
Application.ScreenUpdating = True

Protect Password:="password"


End With
End Sub

What I would like to do is instead of the "xlDialogPrinterSetup" showing I would like the code to do (this is the part some people struggle to do, some people thought I was talking totally alien to them)-

double sided on the short edge.
colour.
A3.
use tray 2.
and use a named printer (because some people using the spreadsheet will have a different default printer).

I know it might seem pretty basic to select the printer settings for the page set up but I need people to get this right. These are legal documents once they are printed off and signed.

As usual all the help is appreciated.

Craig.

 

[Andrew Poulsom]

There is a series of articles on controlling the printer from VBA here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=116
http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=183

 

[Craig1]

Thanks for that Andrew.

I have had a look and whilst I know a little about VBA this seems a bit too technical for me to know how to interpret this and input into my code.
I know it all depends who is writing the code as to how it looks.
Any idea's how I would get these suggestions into my code.

Thanks again.
Craig.

 

[Andrew Poulsom]

Controlling the printer with VBA isn't trivial. If you don't feel comfortable with the sample code I posted I suggest you leave things as they are.

 

[Kyle123]

Printers from Excel are a pain, I'll get you started but a lot of it will be trial and error, this will get you the printer & port so you can at least change the active printer:

Public Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long


Public Declare Function RegQueryValueEx Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, lpData As Any, _
dwSize As Long) As Long


Public Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long


Private Const dhcKeyAllAccess = &H2003F
Private Const HKEY_CURRENT_USER = &H80000001
Private Const dhcRegSz As String = 1


Function GetPrinter(PrinterName As String) As String
    Dim hKeyPrinter As Long
    Dim lngResult As Long
    Dim strBuffer As String
    Dim cb As Long
    
    lngResult = RegOpenKeyEx(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", 0&, dhcKeyAllAccess, hKeyPrinter)
    
    If lngResult = 0 Then
        strBuffer = Space(255)
        cb = Len(strBuffer)
        
        lngResult = RegQueryValueEx(hKeyPrinter, PrinterName, 0&, dhcRegSz, ByVal strBuffer, cb)


        If lngResult = 0 Then GetPrinter = PrinterName & " on " & Right(Left(strBuffer, cb), 6)
        
        lngResult = RegCloseKey(hKeyPrinter)
    End If


End Function

That goes in a standard module, it can then be called like this:

application.activeprinter = GetPrinter("\\Server\Printername")

In terms of getting the trays follow the first link Andrew posted and work through it

 

[Craig1]

Thanks for that guys, I'll give it a go at the weekend when I'm back at work.

What if I just wanted to do all the rest without choosing the printer, would that be easy enough.

Really appreciate the help so far.

Craig.

Craig.

 

[Kyle123]

not really, choosing the printer's the easy bit