Problem setting printer in excel

[Fozzie22]

when i try to run the following code i get a runtime error 1004 "Unable to set active printer property of the application class" at the line "ExcelObject.ActivePrinter = "\\WS149.Logitech.local\DYMO LabelManager PC" ".

The code is meant to open an excel sheet, add a template and put some numbers in, print to the label printer(which is not the default printer) and then close again. if i leave this problem line out it works fine but prints to the default printer. Any ideas anyone?

Dim ExcelObject As Object
Dim XLSheet As Object
If (FindWindow(lpClassName, 0&) = 0) And (YesNo = vbYes) Then 'check if excel is not open
Set ExcelObject = CreateObject("excel.application")
ExcelObject.Visible = True
ExcelObject.Workbooks.Add (LOGIGOODS_LABEL_TEMPLATE)
Set XLSheet = ExcelObject.Application.ActiveWorkbook.ActiveSheet

For I = 1 To NoOfLabels
With XLSheet
.Cells(1, I).Value = "IG:" & Me.IGNo
.Cells(2, I).Value = "Job:" & Me.JobNo
End With
Next I


ExcelObject.ActivePrinter = "\\WS149.Logitech.local\DYMO LabelManager PC"

XLSheet.PrintOut

ExcelObject.Application.ActiveWorkbook.Close SaveChanges:=False
ExcelObject.Application.Quit

Thanks

Anthony

 

[Fozzie22]

Just Incase anyone was wondering about the rest of the code, here is the whole lot.

Private Sub PrintGoodsLabels_Click()

Dim NoOfLabels As Long
Dim I As Integer
Dim ExcelObject As Object
Dim XLSheet As Object
Dim boolAddAddress As Boolean
Dim Response As String
Dim YesNo As Integer

Const lpClassName = "XLMain"
YesNo = vbYes

HowMany:
Response = InputBox("How Many Labels would you like to print for" & _
Chr(13) & Chr(10) & "Job Number: " & Me.JobNo & _
Chr(13) & Chr(10) & "Inwards Goods Number: " & Me.IGNo, "Print Labels")

If (Not Response = "") And (IsNumeric(Response)) Then
NoOfLabels = Eval(Response)
NoOfLabels = Round(NoOfLabels)

If (NoOfLabels < 0) Then
MsgBox "Please enter a positive number", vbOKOnly, "Error"
GoTo HowMany
End If

If NoOfLabels > 256 Then
YesNo = MsgBox("You can only Print 256 labels at a time." & _
Chr(13) & Chr(10) & "Would you like to Print 256 Labels?", vbYesNo, "Error")
If YesNo = vbYes Then
NoOfLabels = 256
End If
ElseIf NoOfLabels > 50 Then
YesNo = MsgBox("Are you sure you want to print " & NoOfLabels & " labels?", vbYesNo)
If YesNo = vbYes Then
NoOfLabels = 256
End If
End If

If (FindWindow(lpClassName, 0&) = 0) And (YesNo = vbYes) Then 'check if excel is not open
Set ExcelObject = CreateObject("excel.application")
ExcelObject.Visible = True
ExcelObject.Workbooks.Add (LOGIGOODS_LABEL_TEMPLATE)
Set XLSheet = ExcelObject.Application.ActiveWorkbook.ActiveSheet

For I = 1 To NoOfLabels
With XLSheet
.Cells(1, I).Value = "IG:" & Me.IGNo
.Cells(2, I).Value = "Job:" & Me.JobNo
End With
Next I

ExcelObject.ActivePrinter = "\\WS149.Logitech.local\DYMO LabelManager PC"

XLSheet.PrintOut

ExcelObject.Application.ActiveWorkbook.Close SaveChanges:=False
ExcelObject.Application.Quit

ElseIf YesNo = vbNo Then
GoTo HowMany

Else
MsgBox "Please Close Microsoft Excel And Try Again", vbOKOnly, "Error"

End If

ElseIf (Response = "") Then

Else
MsgBox "Please enter a number.", vbOKOnly, "Error"
GoTo HowMany
End If

End Sub

Thanks again

Anthony