Excel object stays open

When you run the below code it will crash the 2nd time you click on the command1 button.  With the error "run-time" error 91.  Object variable or with block variable not set"


Private Sub Command1_Click()
    Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks     As Excel.Worksheet
     
     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open("c:\testii\workbook1.xlsx")
     Set wks = wkb.Worksheets(1)
     
     wks.Name = "My New Name"
     
           ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True
     wkb.Close True
     
     Set wks = Nothing
     Set wkb = Nothing
     
     xls.Quit
     
     Set xls = Nothing
End Sub
rjefAsked:
Who is Participating?
 
KimputerConnect With a Mentor Commented:
change again

       wkb.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

0
 
KimputerCommented:
There's nothing wrong with the code. What happens if you step through the code twice (F8)? Which line gives the error?
0
 
rjefAuthor Commented:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
         IgnorePrintAreas:=True
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
KimputerCommented:
Try again with:

           wks.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

0
 
rjefAuthor Commented:
nothing prints
0
 
rjefAuthor Commented:
ok so that worked.  so what is the issue?
0
 
rjefAuthor Commented:
I take that back.  nothing printed.
0
 
Martin LissOlder than dirtCommented:
I believe line 16 was misplaced.
Private Sub Command1_Click()
    Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks     As Excel.Worksheet
     
     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open("c:\solutions\Prashansa.xlsm")
     Set wks = wkb.Worksheets(1)
     
     wks.Name = "My New Name"
     
           ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True
     wkb.Close True
     
     xls.Quit
     
     Set wks = Nothing
     Set wkb = Nothing
     
     Set xls = Nothing
End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
Here you should have a reference to the instance of Excel you created earlier or the sheet(s) you want to print.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

What sheet(s) do you want to print?

PS Where are you running this code?
0
 
rjefAuthor Commented:
running in vb6
sheet1?
0
 
rjefAuthor Commented:
The sheet I want to print is "My New Name"
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Have you tried?
wks.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

0
 
Helen FeddemaCommented:
You might need to activate the sheet before printing it.
0
 
Martin LissOlder than dirtCommented:
Did you try the change I posted in post ID: 41789812? I ask because that solved the problem for me.
0
 
NorieVBA ExpertCommented:
wks.PrintOut... should work.

Are you sure nothing is being sent to the printer?

How are you running the code?
0
 
rjefAuthor Commented:
this finally worked.  

not sure who came up with it?

you all tell me


Private Sub Command1_Click()

    Dim xls     As Excel.Application
     Dim wkb     As Excel.Workbook
     Dim wks     As Excel.Worksheet
     
     Set xls = New Excel.Application
     Set wkb = xls.Workbooks.Open("c:\testii\workbook1.xlsx")
     Set wks = wkb.Worksheets(1)
     
     wks.Name = "My New Name"
     
wks.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True
       
       
     wkb.Close True
     
     xls.Quit
     
     Set wks = Nothing
     Set wkb = Nothing
     
     Set xls = Nothing
End Sub
0
 
NorieVBA ExpertCommented:
Well it was Kimputer that originally suggested using wks.PrintOut...
0
 
rjefAuthor Commented:
the reason it wasn't printing at one point was because it was empty.!!
0
 
rjefAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.