Choosing a printer from VBA?

[XelaIrodavlas]

Hi All,

I've been asked to put together a function that prints two different reports and up to two hyperlinked documents on our network (based on a selected combo box item).

The idea is to save users from having to locate each report individually - Easy enough I thought, I just tell access where to find each report, and print without opening the dialog box for any of them. See code below.

 Private Function PrintAllDocs()
'Print the Selected WPS, Weld Card, and Associated WPAR's
If Len(Me!Combo74 & vbNullString) = 0 Then Exit Function 'do nothing if none chosen
mResponse = MsgBox("This will print a Weld Card, WPS, and any related WPAR's for the selected WPS." & vbCrLf & vbCrLf & _
                   "Note: This will print from the last known printer for each form." _
                   , vbYesNo + vbInformation, "Caution")
If mResponse <> vbYes Then Exit Function
 
 '1. Print Weld Card (As per normal button):
DoCmd.Close acReport, "A4CustomWPSGrid"
DoCmd.Close acReport, "A4CustomWPSNoGrid"
If Me!Option76 = True Then
DoCmd.OpenReport "A4CustomWPSGrid", acViewNormal, "RevisionID = " & Me!Combo78 'Grid
Else
DoCmd.OpenReport "A4CustomWPSNoGrid", acViewNormal, , "RevisionID = " & Me!Combo78  'No Grid
End If
 
 '2. Print The WPS
DoCmd.Close acReport, "WPSNoWeldCard"
DoCmd.OpenReport "WPSNoWeldCard", acViewNormal, , "RevisionID = " & Me!Combo78
 
 '3. Find, Open and Print the WPAR PDF(s)
Dim vLink As String
Dim MyBrowser As SHDocVw.InternetExplorer
Set MyBrowser = New SHDocVw.InternetExplorer
 If Len(Me!Combo74.Column(2) & vbNullString) <> 0 Then 'Check first WPARID field is not null
DoCmd.OpenForm "WPARNew", , , "WPARID = " & Me!Combo74.Column(2)
If Len(Forms!WPARNew!Text77 & vbNullString) <> 0 Then 'Check there is a hyperlink to this WPAR.
    vLink = HyperlinkPart(Forms!WPARNew!Text77, acAddress)
    'display the file
    'MyBrowser.Visible = True
    'MyBrowser.Navigate vLink
    'print it
    ExecuteFile vLink, PrintFile
    Else
    MsgBox "Error finding Scan of WPAR " & Forms!WPARNew!Text38
    End If
DoCmd.Close acForm, "WPARNew"
End If

 '3a. If Second WPARID field is not null then repeat for that
If Len(Me!Combo74.Column(3) & vbNullString) <> 0 Then 'Check first WPARID field is not null
DoCmd.OpenForm "WPARNew", , , "WPARID = " & Me!Combo74.Column(3)
If Len(Forms!WPARNew!Text77 & vbNullString) <> 0 Then 'Check there is a hyperlink to this WPAR.
    vLink = HyperlinkPart(Forms!WPARNew!Text77, acAddress)
    'display the file
    ''MyBrowser.Visible = True
    ''MyBrowser.Navigate vLink
    'print it
    ExecuteFile vLink, PrintFile
    Else
    MsgBox "Error finding Scan of WPAR " & Forms!WPARNew!Text38
    End If
DoCmd.Close acForm, "WPARNew"
End If
 DoCmd.Close acForm, "PrintFrm"
End Function

This works ok, except when printing this way, it prints each report to whatever printer/settings were chosen the last time that report was printed, which can be different every time, this is resulting in very odd events such as two reports sent to printer, and one to the pdf writer, or one report from one printer, and the others from down the hallway!

This may be a big ask, but does anyone know if its possible to open the print dialog menu once, and then apply the selected settings to the other two reports as well?

I'm currently toying with application.filedialog but this is very new ground for me...

Any help appreciated

Alex S

 

[Isskint]

Hi

Have a look at Application.Printers() method. You can specify which printer to output to. Not sure if you can incorporate it in your scenario.

 

[Ranman256]

Can you fill a combo box with the printers and choose?
Can you fill a combo box with report,printer. So when user picks report, it also picks the printer?

sub PrintRpt (pvRpt, pvPtr)
    dim rpt

    DoCmd.OpenReport pvRpt, acViewDesign
       Set rpt = Reports(pvRpt)
       rpt.Printer = pvPtr
    DoCmd.Close acReport, rpt.Name, acSaveYes

    DoCmd.OpenReport vRpt
 end sub

 

[gemma-the-husky]

the easiest way to use printers is to temporarily set the default printer to a particular printer. when you are finished clear this setting, and it goes back to the system default.

 

[XelaIrodavlas]

Thanks for the replies this is all very helpful

I see what you're saying - if I can set the default printer before hand then that should do the job. However part of the issue is that my users often move between different sites and printers, so setting it to just one printer wouldn't work. One way or another I still need to give them a choice of which printer to use.

A combo box could work for this but there will always be a chance they move to a different site/new printer/client-office which isn't on the list.

Unless I try to auto-populate said list with the names of all available printers.... I will have to look into this.

Otherwise I am stuck trying to capture the properties used in the first print, after which I could apply them to other prints in the code (we'll call this plan B)

FYI: helpful link for Application.Printer() property for anyone else reading: https://msdn.microsoft.com/en-us/library/office/ff821394.aspx

 

[Isskint]

Here is a useful way to obtain the list of all the available printers

For Each prt In Application.Printers
MsgBox prt.DeviceName
Next prt

You would just need to change the MsgBox line to assign the value to a listbox or combobox for the user to select

 

[XelaIrodavlas]

Hi IssKint, I've tried to change as you suggested and added the following just before the first print:

For Each prt In Application.Printers
 '''Where Me!PrinterList is a combo box listing all available printers.
        If prt.DeviceName = Me!PrinterList Then
            Set Application.Printer = prt 
            Exit For
        End If
Next prt

It's kind of working - I can use this function to change where the first document prints, but after that doc it reverts to default settings... the second and any subsequent PDF's are all printed to wherever they were last printed manually, not the selected printer in me!PrintersList.

I tried repeating the above snippet above each print function, but that doesn't appear to work... very strange.

For reference here's the code as it stands:

Private Function PrintAllDocs()
'Print the Selected WPS, Weld Card, and Associated WPAR's
If Len(Me!Combo74 & vbNullString) = 0 Then Exit Function
mResponse = MsgBox("This will print a Weld Card, WPS, and any related WPAR's for the selected WPS." & vbCrLf & vbCrLf & _
                   "Note: This will print from the last known printer for each form." _
                   , vbYesNo + vbInformation, "Caution")
If mResponse <> vbYes Then Exit Function
 For Each prt In Application.Printers
        If prt.DeviceName = Me!PrinterList Then
            Set Application.Printer = prt 'if you chose a valid printer from the dropdown, set it to the default.
            Exit For
        End If
Next prt
 
 '1. Print Weld Card (As per normal button):
DoCmd.Close acReport, "A4CustomWPSGrid"
DoCmd.Close acReport, "A4CustomWPSNoGrid"
If Me!Option76 = True Then
DoCmd.OpenReport "A4CustomWPSGrid", acViewNormal, "RevisionID = " & Me!Combo78 'Grid
Else
DoCmd.OpenReport "A4CustomWPSNoGrid", acViewNormal, , "RevisionID = " & Me!Combo78  'No Grid
End If
 
 '2. Print The WPS
DoCmd.Close acReport, "WPSNoWeldCard" 'this seems to be ignoring thedefault printer ive just set...
DoCmd.OpenReport "WPSNoWeldCard", acViewNormal, , "RevisionID = " & Me!Combo78
 
 '3. Find, Open and Print the WPAR PDF's
Dim vLink As String
Dim MyBrowser As SHDocVw.InternetExplorer
Set MyBrowser = New SHDocVw.InternetExplorer
If Len(Me!Combo74.Column(2) & vbNullString) <> 0 Then 'Check first WPARID field is not null
DoCmd.OpenForm "WPARNew", , , "WPARID = " & Me!Combo74.Column(2)
If Len(Forms!WPARNew!Text77 & vbNullString) <> 0 Then 'Check there is a hyperlink to this WPAR.
    vLink = HyperlinkPart(Forms!WPARNew!Text77, acAddress)
    ExecuteFile vLink, PrintFile
    Else
    MsgBox "Error finding Scan of WPAR " & Forms!WPARNew!Text38
    End If
DoCmd.Close acForm, "WPARNew"
End If
 
 '3a. If Second WPARID field is not null then repeat for that
If Len(Me!Combo74.Column(3) & vbNullString) <> 0 Then 'Check first WPARID field is not null
DoCmd.OpenForm "WPARNew", , , "WPARID = " & Me!Combo74.Column(3)
If Len(Forms!WPARNew!Text77 & vbNullString) <> 0 Then 'Check there is a hyperlink to this WPAR.
    vLink = HyperlinkPart(Forms!WPARNew!Text77, acAddress)
    ExecuteFile vLink, PrintFile
    Else
    MsgBox "Error finding Scan of WPAR " & Forms!WPARNew!Text38
    End If
DoCmd.Close acForm, "WPARNew"
End If
 DoCmd.Close acForm, "PrintFrm"
 End Function

 

[gemma-the-husky]

your code should work.

after you have finished just

Set Application.Printer = nothing

to return to the default printer.


note that the printer name is case sensitive, so

Set Application.Printer = printers(selectedprintername)

may not work, if there is a case difference with selectedprintername and the actual printer ame. This is why it's easier to iterate the collection of printers.

note that the printer selection will be different for each user - users should certainly be using a discrete copy of the database. If you are all sharing a single database, then the printer setting may well not work correctly.

 

[XelaIrodavlas]

Hi Dave (and Gemma), interesting point about caps lock, but this seems to be the only way I can get it to work I just switched out the code from my previous post for the below and it's now working for both the Access reports.

Not sure what was wrong with the 'for each method', but frankly its more onerous than this new one anyway so its probably for the best.

Dim vPrinter As String
vPrinter = Me!PrinterList
Set Application.Printer = Application.Printers(vPrinter)

It works for the Access reports - It still does nothing for the PDF documents found by hyperlink. I suspect that's because I use explorer to open them, and the settings to print from this are different to Application.Printer.

Edit for anyone reading: You can set the printer on ShellExecute by using PrintTo instead of just print, then set the printer in the format below.

ShellExecute 0, sAction, fileName, """" & vPrinter & """", "", SW_SHOWNORMAL 
'Where sAction = "PrintTo",
'FileName = the full hyperlink to the doc including file type,
'vPrinter = string containing the name of the printer you want to use.

There's a bunch more code to make this work but i wont post unless someone needs it

P.S. Here is the code I used to populate the combo box, it should be 'case safe':

 Private Sub Form_Current()
 Me!PrinterList.RowSource = ""
For Each prt In Application.Printers
Me!PrinterList.RowSource = Me!PrinterList.RowSource & "; " & prt.DeviceName
Next prt
 Me!PrinterList.RowSource = Mid(Me!PrinterList.RowSource, 2) 'shave off the extra ;
 End Sub

 

[gemma-the-husky]

glad you got it working.

The issue with case is that, say, if you have a printer called "Epson", then setting this will fail

Set Application.Printer = Application.Printers("epson")

However, if you test all the printers until you find one with a name matching "epson", then this works irrespective of the case. The "printer" is a windows object, but comparing text is a VBA function. I think that is the distinction. So the first example you used should have worked. There must have been something else going on.