rjef
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:\tes tii\workbo ok1.xlsx")
Set wks = wkb.Worksheets(1)
wks.Name = "My New Name"
ActiveWindow.SelectedSheet s.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=True
wkb.Close True
Set wks = Nothing
Set wkb = Nothing
xls.Quit
Set xls = Nothing
End Sub
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:\tes
Set wks = wkb.Worksheets(1)
wks.Name = "My New Name"
ActiveWindow.SelectedSheet
IgnorePrintAreas:=True
wkb.Close True
Set wks = Nothing
Set wkb = Nothing
xls.Quit
Set xls = Nothing
End Sub
There's nothing wrong with the code. What happens if you step through the code twice (F8)? Which line gives the error?
ASKER
ActiveWindow.SelectedSheet s.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=True
IgnorePrintAreas:=True
Try again with:
wks.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=True
ASKER
nothing prints
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok so that worked. so what is the issue?
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
Here you should have a reference to the instance of Excel you created earlier or the sheet(s) you want to print.
PS Where are you running this code?
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=True
What sheet(s) do you want to print?PS Where are you running this code?
ASKER
running in vb6
sheet1?
sheet1?
ASKER
The sheet I want to print is "My New Name"
Have you tried?
wks.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=True
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?
Are you sure nothing is being sent to the printer?
How are you running the code?
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:\tes tii\workbo ok1.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
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:\tes
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...
ASKER
the reason it wasn't printing at one point was because it was empty.!!
ASKER
thanks