Solved

Excel object stays open

Posted on 2016-09-08
19
92 Views
Last Modified: 2016-09-08
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
0
Comment
Question by:rjef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 3
  • +3
19 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 41789705
There's nothing wrong with the code. What happens if you step through the code twice (F8)? Which line gives the error?
0
 

Author Comment

by:rjef
ID: 41789726
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
         IgnorePrintAreas:=True
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 41789762
Try again with:

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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rjef
ID: 41789770
nothing prints
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41789786
change again

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

Open in new window

0
 

Author Comment

by:rjef
ID: 41789794
ok so that worked.  so what is the issue?
0
 

Author Comment

by:rjef
ID: 41789795
I take that back.  nothing printed.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41789812
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
 
LVL 34

Expert Comment

by:Norie
ID: 41789824
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
 

Author Comment

by:rjef
ID: 41789892
running in vb6
sheet1?
0
 

Author Comment

by:rjef
ID: 41789898
The sheet I want to print is "My New Name"
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789930
Have you tried?
wks.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 41790055
You might need to activate the sheet before printing it.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41790061
Did you try the change I posted in post ID: 41789812? I ask because that solved the problem for me.
0
 
LVL 34

Expert Comment

by:Norie
ID: 41790093
wks.PrintOut... should work.

Are you sure nothing is being sent to the printer?

How are you running the code?
0
 

Author Comment

by:rjef
ID: 41790167
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
 
LVL 34

Expert Comment

by:Norie
ID: 41790179
Well it was Kimputer that originally suggested using wks.PrintOut...
0
 

Author Comment

by:rjef
ID: 41790197
the reason it wasn't printing at one point was because it was empty.!!
0
 

Author Closing Comment

by:rjef
ID: 41790198
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question