Cannot quit Excel called from VB6

28 Feb 2007 4:34 PM

jawf

I cannot seem to get the Excel instance to quit after I am done with
it, except to close my VB6 program. The below is what I am doing after
adding in the Microsoft Excel 11.0 Object Library reference:

Public Sub ReadSpreadSheet ()

Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Dim Book As String

Dim Val as varant
Din Y as integer

   Book = "Book1.xls"
   Set xlwbook = xl.Workbooks.Open("c:\BT\" & Book)
   Set xlsheet = xlwbook.Sheets.item(2)
   xlsheet.Activate
    For y = xlsheet.UsedRange.Rows.Count To 1 Step -1
          Val = Cells(y, 1).Value
          Debug.Print Val
     Next y

   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
   Set xlwbook = Nothing
   Set xlsheet = Nothing
   xl.Quit
   Set xl = Nothing
End Sub

I am callng this routine from a form.
Any suggestions?

Thanks, Joe

28 Feb 2007 5:02 PM

Saga

See below

Saga

--

<jawNONfSPAMME@pge.com> wrote in message

news:1172680468.077124.243200@q2g2000cwa.googlegroups.com...
>I cannot seem to get the Excel instance to quit after I am done with
> it, except to close my VB6 program. The below is what I am doing after
> adding in the Microsoft Excel 11.0 Object Library reference:
>
> Public Sub ReadSpreadSheet ()
>
> Dim xl As New Excel.Application

I would change that to

Dim xl As Excel.Application

> Dim xlsheet As Excel.Worksheet
> Dim xlwbook As Excel.Workbook
> Dim Book As String
>
>
> Dim Val as varant

'Did you mean Variant?

> Din Y as integer
>

Add this:

set xl = New Excel.Application

I would also look into late binding. There is another thread that touches
this subject a little before this one.

Show quoteHide quote

>   Book = "Book1.xls"
>   Set xlwbook = xl.Workbooks.Open("c:\BT\" & Book)
>   Set xlsheet = xlwbook.Sheets.item(2)
>   xlsheet.Activate
>    For y = xlsheet.UsedRange.Rows.Count To 1 Step -1
>          Val = Cells(y, 1).Value
>          Debug.Print Val
>     Next y
>
>   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
>   Set xlwbook = Nothing
>   Set xlsheet = Nothing
>   xl.Quit
>   Set xl = Nothing
> End Sub
>
> I am callng this routine from a form.
> Any suggestions?
>
> Thanks, Joe
>

28 Feb 2007 5:07 PM

Robert Morley

I don't know if it'll help, but try reversing the order of setting xlsheet &
xlwbook to Nothing. The Sheet is a sub-object of the workbook, so it should
be set to Nothing first. In fact, you may even want to try that before
closing the workbook.

Rob

<j***@pge.com> wrote in message
Show quoteHide quote

news:1172680468.077124.243200@q2g2000cwa.googlegroups.com...
>I cannot seem to get the Excel instance to quit after I am done with
> it, except to close my VB6 program. The below is what I am doing after
> adding in the Microsoft Excel 11.0 Object Library reference:
>
> Public Sub ReadSpreadSheet ()
>
> Dim xl As New Excel.Application
> Dim xlsheet As Excel.Worksheet
> Dim xlwbook As Excel.Workbook
> Dim Book As String
>
>
> Dim Val as varant
> Din Y as integer
>
>   Book = "Book1.xls"
>   Set xlwbook = xl.Workbooks.Open("c:\BT\" & Book)
>   Set xlsheet = xlwbook.Sheets.item(2)
>   xlsheet.Activate
>    For y = xlsheet.UsedRange.Rows.Count To 1 Step -1
>          Val = Cells(y, 1).Value
>          Debug.Print Val
>     Next y
>
>   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
>   Set xlwbook = Nothing
>   Set xlsheet = Nothing
>   xl.Quit
>   Set xl = Nothing
> End Sub
>
> I am callng this routine from a form.
> Any suggestions?
>
> Thanks, Joe
>

28 Feb 2007 5:17 PM

Jwatkins

On Feb 28, 9:07 am, "Robert Morley"
<rmor***@magma.ca.N0.Freak1n.sparn> wrote:
Show quoteHide quote

> I don't know if it'll help, but try reversing the order of setting xlsheet &
> xlwbook to Nothing. The Sheet is a sub-object of the workbook, so it should
> be set to Nothing first. In fact, you may even want to try that before
> closing the workbook.
>
> Rob
>
> <j***@pge.com> wrote in message
>
> news:1172680468.077124.243200@q2g2000cwa.googlegroups.com...
>
>
>
> >I cannot seem to get the Excel instance to quit after I am done with
> > it, except to close my VB6 program. The below is what I am doing after
> > adding in the Microsoft Excel 11.0 Object Library reference:
>
> > Public Sub ReadSpreadSheet ()
>
> > Dim xl As New Excel.Application
> > Dim xlsheet As Excel.Worksheet
> > Dim xlwbook As Excel.Workbook
> > Dim Book As String
>
> > Dim Val as varant
> > Din Y as integer
>
> >   Book = "Book1.xls"
> >   Set xlwbook = xl.Workbooks.Open("c:\BT\" & Book)
> >   Set xlsheet = xlwbook.Sheets.item(2)
> >   xlsheet.Activate
> >    For y = xlsheet.UsedRange.Rows.Count To 1 Step -1
> >          Val = Cells(y, 1).Value
> >          Debug.Print Val
> >     Next y
>
> >   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
> >   Set xlwbook = Nothing
> >   Set xlsheet = Nothing
> >   xl.Quit
> >   Set xl = Nothing
> > End Sub
>
> > I am callng this routine from a form.
> > Any suggestions?
>
> > Thanks, Joe- Hide quoted text -
>
> - Show quoted text -

Thanks to everyone. I was still researching as I wrote my original
question. After digging a lot I was able to find out what worked.

In every reference to Cells( y,1) I changed to xlsheet.Cells (y,1) and
that fixed the issue. It seems that you must be very explicit when
referencing Excel objects, etc.
Thanks, again to all that made suggestions. I will try and tidy my
code a bit.

Joe

28 Feb 2007 5:30 PM

Robert Morley

Hadn't noticed that. I'm surprised "Cells" worked at all on its own. Glad
to hear you figured it out.

Rob

Show quoteHide quote

"Jwatkins" <j***@pge.com> wrote in message
news:1172683026.091186.243420@k78g2000cwa.googlegroups.com...
> On Feb 28, 9:07 am, "Robert Morley"
> <rmor***@magma.ca.N0.Freak1n.sparn> wrote:
>> I don't know if it'll help, but try reversing the order of setting
>> xlsheet &
>> xlwbook to Nothing. The Sheet is a sub-object of the workbook, so it
>> should
>> be set to Nothing first. In fact, you may even want to try that before
>> closing the workbook.
>>
>> Rob
>>
>> <j***@pge.com> wrote in message
>>
>> news:1172680468.077124.243200@q2g2000cwa.googlegroups.com...
>>
>>
>>
>> >I cannot seem to get the Excel instance to quit after I am done with
>> > it, except to close my VB6 program. The below is what I am doing after
>> > adding in the Microsoft Excel 11.0 Object Library reference:
>>
>> > Public Sub ReadSpreadSheet ()
>>
>> > Dim xl As New Excel.Application
>> > Dim xlsheet As Excel.Worksheet
>> > Dim xlwbook As Excel.Workbook
>> > Dim Book As String
>>
>> > Dim Val as varant
>> > Din Y as integer
>>
>> >   Book = "Book1.xls"
>> >   Set xlwbook = xl.Workbooks.Open("c:\BT\" & Book)
>> >   Set xlsheet = xlwbook.Sheets.item(2)
>> >   xlsheet.Activate
>> >    For y = xlsheet.UsedRange.Rows.Count To 1 Step -1
>> >          Val = Cells(y, 1).Value
>> >          Debug.Print Val
>> >     Next y
>>
>> >   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
>> >   Set xlwbook = Nothing
>> >   Set xlsheet = Nothing
>> >   xl.Quit
>> >   Set xl = Nothing
>> > End Sub
>>
>> > I am callng this routine from a form.
>> > Any suggestions?
>>
>> > Thanks, Joe- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks to everyone. I was still researching as I wrote my original
> question. After digging a lot I was able to find out what worked.
>
> In every reference to Cells( y,1) I changed to xlsheet.Cells (y,1) and
> that fixed the issue. It seems that you must be very explicit when
> referencing Excel objects, etc.
> Thanks, again to all that made suggestions. I will try and tidy my
> code a bit.
>
> Joe
>

28 Feb 2007 5:45 PM

Bob Butler

"Jwatkins" <j***@pge.com> wrote in message
news:1172683026.091186.243420@k78g2000cwa.googlegroups.com
> Thanks to everyone. I was still researching as I wrote my original
> question. After digging a lot I was able to find out what worked.
>
> In every reference to Cells( y,1) I changed to xlsheet.Cells (y,1) and
> that fixed the issue. It seems that you must be very explicit when
> referencing Excel objects, etc.
> Thanks, again to all that made suggestions. I will try and tidy my
> code a bit.

Yes, always try to avoid implicit references with Excel; it's a bear about
shutting down sometimes.

I'd also change this:
   xl.ActiveWorkbook.Close False, "C:\BT\" & Book
   Set xlwbook = Nothing
   Set xlsheet = Nothing
to this:
   Set xlsheet = Nothing
   xlwbook.Close False
   Set xlwbook = Nothing

and this:
   xl.Quit
to this:
If xl.Workbooks.Count=0 Then
    xl.Quit
Else
    xl.Visible=True
End If
That will prevent excel from shutting down if your user happens to have it
open outside your app and will let you get at the instance to close it
manually if it does get mixed up and refuse to quit.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."