Solved

Excel object stays open

Posted on 2016-09-08
19
64 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
  • 9
  • 3
  • 3
  • +3
19 Comments
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
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
Comment Utility
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
         IgnorePrintAreas:=True
0
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
Try again with:

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

Open in new window

0
 

Author Comment

by:rjef
Comment Utility
nothing prints
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
Comment Utility
change again

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

Open in new window

0
 

Author Comment

by:rjef
Comment Utility
ok so that worked.  so what is the issue?
0
 

Author Comment

by:rjef
Comment Utility
I take that back.  nothing printed.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:rjef
Comment Utility
running in vb6
sheet1?
0
 

Author Comment

by:rjef
Comment Utility
The sheet I want to print is "My New Name"
0
 
LVL 28

Expert Comment

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

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
You might need to activate the sheet before printing it.
0
 
LVL 45

Expert Comment

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

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
Well it was Kimputer that originally suggested using wks.PrintOut...
0
 

Author Comment

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

Author Closing Comment

by:rjef
Comment Utility
thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now