VB & Excel driving me mad! 
Author Message
 VB & Excel driving me mad!

Hi all!

I wonder if you can help me with this extremely annoying problem. Apologies
if this has appeared before but I couldn't find anything on it in Dejanews.

I'm using Excel in my VB app to produce a report to be printed out or mailed
to other users. I use the following code to get it going:

Dim xlapp as Object

Sub StartExcel()

    set xlapp = CreatObject("excel.application")

    ' If I want to see it
    xlapp.Visible = True

End Sub

That's fine and I fill out the appropriate Workbook that I open. I save this
and close the workbook. Then I exit Excel as follows:

Sub QuitExcel()

    xlapp.Quit

    set xlapp = Nothing

End Sub

Now this seems fine to me and would normally close down Excel. I use Windows
NT and through the Task Manager can see each process. When I execute the
above proc and try to exit Excel, it is still there in the process list!

What is going on here? Please can anyone help me? Do you think I have to use
the WinAPI to terminate the process?

Any help would be greatly appreciated.

Regards

Terry Maguire
IIU
IFSC House
Custom House Quay
Dublin 1
Ireland

<Remove nospam from address to mail>



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Sounds like it could be a scope problem with your Xlapp variable. What is
the scope of your dim Xlapp?
I would start by setting the Xlapp as a Public variable in the form where
you use it, then try setting it as a global variable for the project.

Hope this helps

Quote:

>Hi all!

>I wonder if you can help me with this extremely annoying problem. Apologies
>if this has appeared before but I couldn't find anything on it in Dejanews.

>I'm using Excel in my VB app to produce a report to be printed out or
mailed
>to other users. I use the following code to get it going:

>Dim xlapp as Object

>Sub StartExcel()

>    set xlapp = CreatObject("excel.application")

>    ' If I want to see it
>    xlapp.Visible = True

>End Sub

>That's fine and I fill out the appropriate Workbook that I open. I save
this
>and close the workbook. Then I exit Excel as follows:

>Sub QuitExcel()

>    xlapp.Quit

>    set xlapp = Nothing

>End Sub

>Now this seems fine to me and would normally close down Excel. I use
Windows
>NT and through the Task Manager can see each process. When I execute the
>above proc and try to exit Excel, it is still there in the process list!

>What is going on here? Please can anyone help me? Do you think I have to
use
>the WinAPI to terminate the process?

>Any help would be greatly appreciated.

>Regards

>Terry Maguire
>IIU
>IFSC House
>Custom House Quay
>Dublin 1
>Ireland

><Remove nospam from address to mail>



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!

Robert

Thanx for the suggestion but I use Global scope so that shouldn't be a
problem.

regards

Terry



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Reduce the scope of xlapp object variable to local variable and pass it in
argument to different functions.

Quote:

>Hi all!

>I wonder if you can help me with this extremely annoying problem. Apologies
>if this has appeared before but I couldn't find anything on it in Dejanews.

>I'm using Excel in my VB app to produce a report to be printed out or
mailed
>to other users. I use the following code to get it going:

>Dim xlapp as Object

>Sub StartExcel()

>    set xlapp = CreatObject("excel.application")

>    ' If I want to see it
>    xlapp.Visible = True

>End Sub

>That's fine and I fill out the appropriate Workbook that I open. I save
this
>and close the workbook. Then I exit Excel as follows:

>Sub QuitExcel()

>    xlapp.Quit

>    set xlapp = Nothing

>End Sub

>Now this seems fine to me and would normally close down Excel. I use
Windows
>NT and through the Task Manager can see each process. When I execute the
>above proc and try to exit Excel, it is still there in the process list!

>What is going on here? Please can anyone help me? Do you think I have to
use
>the WinAPI to terminate the process?

>Any help would be greatly appreciated.

>Regards

>Terry Maguire
>IIU
>IFSC House
>Custom House Quay
>Dublin 1
>Ireland

><Remove nospam from address to mail>



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
You should try early binding and see if that fixes it.  For example:

dim XLApp as Excel.Application

-Dan Beacom

On Fri, 19 Feb 1999 11:53:27 -0000, "Terry Maguire"

Quote:

>Hi all!

>I wonder if you can help me with this extremely annoying problem. Apologies
>if this has appeared before but I couldn't find anything on it in Dejanews.

>I'm using Excel in my VB app to produce a report to be printed out or mailed
>to other users. I use the following code to get it going:

>Dim xlapp as Object

>Sub StartExcel()

>    set xlapp = CreatObject("excel.application")

>    ' If I want to see it
>    xlapp.Visible = True

>End Sub

>That's fine and I fill out the appropriate Workbook that I open. I save this
>and close the workbook. Then I exit Excel as follows:

>Sub QuitExcel()

>    xlapp.Quit

>    set xlapp = Nothing

>End Sub

>Now this seems fine to me and would normally close down Excel. I use Windows
>NT and through the Task Manager can see each process. When I execute the
>above proc and try to exit Excel, it is still there in the process list!

>What is going on here? Please can anyone help me? Do you think I have to use
>the WinAPI to terminate the process?

>Any help would be greatly appreciated.

>Regards

>Terry Maguire
>IIU
>IFSC House
>Custom House Quay
>Dublin 1
>Ireland

><Remove nospam from address to mail>



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Nawed & Rich

Thanx for your suggestions but none have helped! It would seem this is some
type of bug perhaps.

Tearing my hair out...

Terry



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!

Dan

Thanx for the suggestion but it still doesn't fix it.

But here an interesting one. When I open the Excel process, open a workbook
and then immediately close the workbooks and quit Excel the process does
disappear!

So if I do now work on the workbook i.e. filling in cells then it works.
Well thats a great help!!

Regards

Terry



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Do you also have workbook or worksheet objects defined that are global? When
you set references to these global variables from the parent application, they
also count as Excel objects and until they are set to Nothing, Excel will
continue to run to support them, even if you close the Excel application.
Quote:

> Dan

> Thanx for the suggestion but it still doesn't fix it.

> But here an interesting one. When I open the Excel process, open a workbook
> and then immediately close the workbooks and quit Excel the process does
> disappear!

> So if I do now work on the workbook i.e. filling in cells then it works.
> Well thats a great help!!

> Regards

> Terry



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!

Bill

No I just have the Excel application object and that's all.

Terry



Tue, 07 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
This code inside a single Form with two command buttons on it works
perfectly for me:

Option Explicit
Dim objExcel As Excel.Application

Private Sub Command1_Click()
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    With objExcel
        .Workbooks.Open "C:\city\test.xls"
    End With
    'Excel shows up with spreadsheet loaded
End Sub

Private Sub Command2_Click()
    objExcel.Quit
    Set objExcel = Nothing
    'Excel disappears and no evidence of it in task list
End Sub

The only reason Excel will not shut down is because somewhere another
program has a reference to its objects. You can close the visible
interface, but Excel will continue to run until all object variables
have been set to Nothing or go out of scope. One way to tell if your VB
program is causing the problem is to watch what happens to Excel when
the VB program terminates normally. If Excel stops after the VB program
is terminated, then your program is holding a reference to Excel
somewhere. Look for the following causes:

Global or module level objects used to hold a reference to Excel, a
Workbook, a Worksheet, a Chart or any other internal Excel object that
has not been set to Nothing prior to executing the Set objExcel =
Nothing line

Dimensioning any Excel object as "New" (Dim objExcel As New
Excel.Application, or Dim objWB As New Excel.Workbook. Always use the
syntax Dim objExcel As Excel.Appplication : Set objExcel = New
Excel.Application

Running a VBA routine within Excel itself that creates a global or
module level Excel object and does not release it

Starting multiple copies of Excel within your program or from the
Desktop after starting an instance of Excel within your VB program

Abnormally terminating your VB program (i.e., by choosing End from the
IDE menus)

HTH

Quote:

> Bill

> No I just have the Excel application object and that's all.

> Terry



Wed, 08 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Hi all

Thanx for all the messages regarding this problem, but unfortunately none of
the solutions worked. After 4 hrs of searching through the code I found that
the following caused a problem (I haven't tried this on any other machines,
so perhaps I have a gremlin):

When I use the following line of code (assuming you've setup the instance of
Excel and called it xlApp):

xlApp.Cells(1,1).Value = xlApp.Cells(1,1).Value & " whatever text!"

my problem occurs. On removing the concat reference I don't have the problem
with the Excel process staying alive.

I intend checking this on another machine, and seeing if the same occurs.

Again thanx again for all the help

Regards

Terry Maguire



Fri, 10 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
OK, I've had a similar problem in the past, sounds like you're almost there.

Is there any particular reason that you must use Excel.Application? You
could, instead, create an Workbook object (I think it's "Excel.Worksheet",
or "Excel.Sheet" in a CreateObject call) object and do whatever you need to
do on it, without ever explicitly referencing Excel.Application. If, for
some reason, you need to actually get to the Excel Application object, then
you can simply use the Parent property of the workbook (or rather, the
Parent.Parent, if you're using a Worksheet object).

In other words, when you create your Excel object, do this...
    objWorkbook = CreateObject("Excel.Worksheet")

instead of...
    objWorkbook = CreateObject("Excel.Application")

I've found that by doing this, as soon as I release the workbook object I
created (set it to Nothing), Excel goes away with no problem.

Quote:

>Dan

>Thanx for the suggestion but it still doesn't fix it.

>But here an interesting one. When I open the Excel process, open a workbook
>and then immediately close the workbooks and quit Excel the process does
>disappear!

>So if I do now work on the workbook i.e. filling in cells then it works.
>Well thats a great help!!

>Regards

>Terry



Fri, 10 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!

I' ve found that if you use Automation with Excel, the Excel process hang around in the background even after you set the Excel object to nothing. This situation does not occur in the final compiled app. The instance of excels dies quite nicely.

Nick

Quote:

>OK, I've had a similar problem in the past, sounds like you're almost there.

>Is there any particular reason that you must use Excel.Application? You
>could, instead, create an Workbook object (I think it's "Excel.Worksheet",
>or "Excel.Sheet" in a CreateObject call) object and do whatever you need to
>do on it, without ever explicitly referencing Excel.Application. If, for
>some reason, you need to actually get to the Excel Application object, then
>you can simply use the Parent property of the workbook (or rather, the
>Parent.Parent, if you're using a Worksheet object).

>In other words, when you create your Excel object, do this...
>    objWorkbook = CreateObject("Excel.Worksheet")

>instead of...
>    objWorkbook = CreateObject("Excel.Application")

>I've found that by doing this, as soon as I release the workbook object I
>created (set it to Nothing), Excel goes away with no problem.


>>Dan

>>Thanx for the suggestion but it still doesn't fix it.

>>But here an interesting one. When I open the Excel process, open a workbook
>>and then immediately close the workbooks and quit Excel the process does
>>disappear!

>>So if I do now work on the workbook i.e. filling in cells then it works.
>>Well thats a great help!!

>>Regards

>>Terry



Sun, 12 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
I have the exact same problem (I use OLE to create XLS files for distribution
around the world) and no matter what I do I still have a process going. If you
figure out a way of killing the process please post it.

Thanks

Quote:

> Hi all

> Thanx for all the messages regarding this problem, but unfortunately none of
> the solutions worked. After 4 hrs of searching through the code I found that
> the following caused a problem (I haven't tried this on any other machines,
> so perhaps I have a gremlin):

> When I use the following line of code (assuming you've setup the instance of
> Excel and called it xlApp):

> xlApp.Cells(1,1).Value = xlApp.Cells(1,1).Value & " whatever text!"

> my problem occurs. On removing the concat reference I don't have the problem
> with the Excel process staying alive.

> I intend checking this on another machine, and seeing if the same occurs.

> Again thanx again for all the help

> Regards

> Terry Maguire



Sun, 12 Aug 2001 03:00:00 GMT  
 VB & Excel driving me mad!
Are you using Win95? I have an app that uses VB and Excel and had the same
program for users using Win95. Those that use NT4.0 have not experienced the
problem!
Quote:

> Hi all!

> I wonder if you can help me with this extremely annoying problem. Apologies
> if this has appeared before but I couldn't find anything on it in Dejanews.

> I'm using Excel in my VB app to produce a report to be printed out or mailed
> to other users. I use the following code to get it going:

> Dim xlapp as Object

> Sub StartExcel()

>     set xlapp = CreatObject("excel.application")

>     ' If I want to see it
>     xlapp.Visible = True

> End Sub

> That's fine and I fill out the appropriate Workbook that I open. I save this
> and close the workbook. Then I exit Excel as follows:

> Sub QuitExcel()

>     xlapp.Quit

>     set xlapp = Nothing

> End Sub

> Now this seems fine to me and would normally close down Excel. I use Windows
> NT and through the Task Manager can see each process. When I execute the
> above proc and try to exit Excel, it is still there in the process list!

> What is going on here? Please can anyone help me? Do you think I have to use
> the WinAPI to terminate the process?

> Any help would be greatly appreciated.

> Regards

> Terry Maguire
> IIU
> IFSC House
> Custom House Quay
> Dublin 1
> Ireland

> <Remove nospam from address to mail>



Sun, 12 Aug 2001 03:00:00 GMT  
 
 [ 24 post ]  Go to page: [1] [2]

 Relevant Pages 

1. VB & Excel driving me mad!

2. VB & Excel driving me mad!

3. VB & Excel driving me mad!

4. VB & Excel driving me mad!

5. This is driving me mad =(.

6. Winsock TCP driving me mad!!!

7. VB5: Icons are driving me mad

8. Error 3041 drives me mad!

9. Crystal reports is driving me mad!!!

10. Select Case statement is driving me mad!

11. Is this doable: drive Excel in VB without Excel installation

12. Registry driving me mad

 

 
Powered by phpBB® Forum Software