Print Macro : Object not set

  • Thread starter Thread starter Ugleeduck
  • Start date Start date
U

Ugleeduck

Hello everyone,

Thanks in advance for reading my post. I'm very new to Excel VBA
programming and having some problem with a code snippet that prints
selected sheets.

I have a form with two list boxes. The left one shows the all the
worksheets and the user can add any or all of these sheets to the list
box on the right. Then I pass the list from the second list box to a
sub procedure taht prints. It prints the selected worksheets, but
everytime I get an error saying " Object variable or with block
variable not set". Here's my code...


Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing
Dim objXLSApp As Object
Dim wbkXLSBook As Workbook
Set objXLSApp = GetObject(, "Excel.Application")
Set wbkXLSBook = objXLSApp.ThisWorkbook

With wbkXLSBook

For i = 0 To lstPrintQue.ListCount - 1
wbkXLSBook.Sheets(lstPrintQue.List(i)).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i

End With

'release object
Set objXLSApp = Nothing

Unload Me


End Sub



I created an Excel appliocation object and then added the selected
worksheets to that application and then printed them out. This looks a
round about way of doing something as simple as printing a few sheets.
Is there a better way? If somebody code show me a code sample I'd
greatly appreciate it. Or, maybe some of you can suggest a few changes
to my existing code?

Again, I really appreciate the help.

Thanks!

A. Rahman
 
You don't need to use createobject or getobject. Application already refers
to the instance of excel with your workbook and thisworkbook is already
defined.

Private Sub cmdPrint_Click()


'create an instanece of excel to initiate printing

With ThisWorkbook

For i = 0 To lstPrintQue.ListCount - 1
if i = 0 then
.Sheets(lstPrintQue.list(i)).Select
else
.Sheets(ListPrintQue.List(i)).Select False
end if
Next i
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
.Worksheets(1).Select
End With
Unload Me
End Sub
 
Back
Top