Specifying Printer in this scenario

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Hi, hoping this may be an easy one, but i'm not sure.

I have a piece of code which transfer data from an excel database to a word document through bookmarks.

Alongside transferring some data, it prints it when done. The issue is It sends it to a default printer, and i would like to choose the printer in which it sends to.

See code below

VBA Code:
Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
Dim Found As Boolean  '******** Indicator for find

'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = frmForm.TextBox27.Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Found = False

Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
 
If WorksOrder = sh.Range("D" & iRow).Value Then

 Found = True

'opening word template

Set wdDOC = wd.Documents.Add("\\server22\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\UKCA  Declaration of Conformity Rev1.docx")

wd.Visible = False

'code to insert values from database to bookmarks in word

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo"
wd.Selection.TypeText text:=sh.Range("C" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo"
wd.Selection.TypeText text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo"
wd.Selection.TypeText text:=sh.Range("O" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo"
wd.Selection.TypeText text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="CertDate"
wd.Selection.TypeText text:=Format(sh.Range("Q" & iRow).Value, "dd/mm/yyyy")

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo2"
wd.Selection.TypeText text:=sh.Range("C" & iRow).Value




'code to delete the existing bookmarks from wordfile

On Error Resume Next

wdDOC.Bookmarks("PartNo").Delete
wdDOC.Bookmarks("SerialNo").Delete
wdDOC.Bookmarks("ModelNo").Delete
wdDOC.Bookmarks("CertDate").Delete
wdDOC.Bookmarks("PartNo2").Delete

'save file with new name

wdDOC.SaveAs2 ThisWorkbook.Path & "\Conformity Forms\" & sh.Range("D" & iRow).Value, 17

'Print document


wdDOC.PrintOut _
ActivePrinter:="\\SERVER11\HP LaserJet Pro M501 PCL 6 on Ne12:"



'close the word file

wdDOC.Close False

'release memory of word doc

Set wdDOC = Nothing


Exit Do

End If

iRow = iRow + 1

Loop

wd.Quit 'close MS Word

Set wd = Nothing 'Release memory allocated to WD

If Found = True Then
MsgBox ("Declaration Of Confirmity Completed")
Else
MsgBox ("Works Order Number Not Found")
End If
End Sub

Note this part, you can see i am trying to specify a printer to use
Code:
wdDOC.PrintOut _
ActivePrinter:="\\SERVER11\HP LaserJet Pro M501 PCL 6 on Ne12:"

However I am having no luck. I assume this is because it is opening word and printing using the default printer of that?

Am i able to dictate what printer it will use on my VBA code here in excel?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top