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
Note this part, you can see i am trying to specify a printer to use
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?
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?