• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

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
0
rjef
Asked:
rjef
  • 9
  • 3
  • 3
  • +3
1 Solution
 
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
 
KimputerCommented:
Try again with:

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

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
rjefAuthor Commented:
nothing prints
0
 
KimputerCommented:
change again

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

Open in new window

0
 
rjefAuthor Commented:
ok so that worked.  so what is the issue?
0
 
rjefAuthor Commented:
I take that back.  nothing printed.
0
 
Martin LissRetired ProgrammerCommented:
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
 
NorieCommented:
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 LissRetired ProgrammerCommented:
Did you try the change I posted in post ID: 41789812? I ask because that solved the problem for me.
0
 
NorieCommented:
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
 
NorieCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now