Link to home
Start Free TrialLog in
Avatar of rjef
rjefFlag for United States of America

asked on

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
Avatar of Kimputer
Kimputer

There's nothing wrong with the code. What happens if you step through the code twice (F8)? Which line gives the error?
Avatar of rjef

ASKER

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
         IgnorePrintAreas:=True
Try again with:

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

Open in new window

Avatar of rjef

ASKER

nothing prints
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rjef

ASKER

ok so that worked.  so what is the issue?
Avatar of rjef

ASKER

I take that back.  nothing printed.
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

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?
Avatar of rjef

ASKER

running in vb6
sheet1?
Avatar of rjef

ASKER

The sheet I want to print is "My New Name"
Have you tried?
wks.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=True

Open in new window

You might need to activate the sheet before printing it.
Did you try the change I posted in post ID: 41789812? I ask because that solved the problem for me.
wks.PrintOut... should work.

Are you sure nothing is being sent to the printer?

How are you running the code?
Avatar of rjef

ASKER

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
Well it was Kimputer that originally suggested using wks.PrintOut...
Avatar of rjef

ASKER

the reason it wasn't printing at one point was because it was empty.!!
Avatar of rjef

ASKER

thanks