By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,854 Members | 869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,854 IT Pros & Developers. It's quick & easy.

Excel Automation

P: n/a
I'm using late binding (I must) to automate Excel.

My code opens Excel after createing and poulating some sheets. My problem is
that when the user finally decides to close Excel its process is left
running until my application closes.

I have tried setting my Excel.Application object to Nothing.

I have tried to then fore the GC into action using:

GC.Collect()
GC.WaitForPendingFinalizers()

I have also tried to:

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcel)

which causes excel to crash (and offer to restart) when the user tries to
shut Excel down.

How can I get round this problem?

--

Cheers,

elziko
Nov 20 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
On Wed, 22 Oct 2003 14:38:45 +0100, "elziko" <el****@NOTSPAMMINGyahoo.co.uk> wrote:

I'm using late binding (I must) to automate Excel.

My code opens Excel after createing and poulating some sheets. My problem is
that when the user finally decides to close Excel its process is left
running until my application closes.

I have tried setting my Excel.Application object to Nothing.

I have tried to then fore the GC into action using:

GC.Collect()
GC.WaitForPendingFinalizers()

I have also tried to:

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcel)

which causes excel to crash (and offer to restart) when the user tries to
shut Excel down.

How can I get round this problem?

I the user is closing Excel, then try setting the UserControl property of the Application object to
True after your automation code has completed.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #2

P: n/a
> I the user is closing Excel, then try setting the UserControl property of
the Application object to
True after your automation code has completed.


Thanks, but the Excel process still remains running after the user closes
Excel. Any other ideas.

--

Cheers,

elziko
Nov 20 '05 #3

P: n/a
I have included here a bit of commented code. As an example I use this code inside a Button_CLick event. It is supposed to check if the user already has an automatable Excel Instance running. If they have then the code adds a new sheet and put soem data into it.

If no such Automatable Excel exists teh code creates a new instance and adds a sheet with the data.

For this example each sheet has a unique name made from Now.TickCount.

Pressing the button several times works great... it adds successive sheets to the document. However, if the user then closes Excel down and we press the button again instead on opening a fresh Excel instance it seems to automate the old one left over (who's process is visible in the Task Manager until the demo app is shut down). The outcome of this is that Excel just hangs.

The rest of this post is my code. Create a windows form application with one form containing one button. In the click event of the button add:

'create new automation object

Dim objExcel As Object

Dim booNew As Boolean

Try

'if there is already an automatable version of excel open then use it

objExcel = GetObject(, "Excel.Application")

booNew = False

Catch ex As Exception

'if theres an error then there is no automatable version available so create one

objExcel = CreateObject("Excel.Application")

booNew = True

End Try

'stop excel displaying alerts to the user during automation

objExcel.DisplayAlerts = False

'if there are no workbooks then create one

Dim objBooks = objExcel.Workbooks

If objBooks.Count < 1 Then

objBooks.Add()

End If

'set up to use the last book available

Dim objBook = objBooks(objBooks.Count)

'remove any sheets we dont want if we are using a new instance of excel

If booNew Then

objBook.WorkSheets("Sheet2").Select()

objExcel.ActiveWindow.SelectedSheets.Delete()

objBook.WorkSheets("Sheet3").Select()

objExcel.ActiveWindow.SelectedSheets.Delete()

End If

Dim objRange As Object

Dim objSheets = objBook.WorkSheets

Dim objSheet As Object

'create a new sheet

objSheet = objSheets.Add()

'name the new sheet

Dim strName As String = Now.Ticks.ToString

Try

'create a new sheet with the name that identifies the test and source table

objSheet.Name = "Test - " + strName

'if it already exists an error will be raised, catch this

Catch

'should create new name but for now will just give an error message

MessageBox.Show("You are trying to add a sheet with the same name as an existing sheet (" + strName + " ) delete or rename the old one first", "Sheet Name Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

objRange = Nothing

objSheet = Nothing

objSheets = Nothing

objBook = Nothing

objBooks = Nothing

objExcel = Nothing

Exit Sub

End Try

'set the window up

objExcel.WindowState = 2

objExcel.Visible = True

objExcel.UserControl = True

'create a header string array for testing

Dim arrHeader As String() = {"COL 1", "COL 2"}

'set up a range that represents the header

objRange = objSheet.Range("A1", Reflection.Missing.Value)

objRange = objRange.Resize(1, arrHeader.GetLength(0))

'add the header in

objRange.Value = arrHeader

'create some data for a test

Dim arrData(2, 1) As Single

arrData(0, 0) = 0.0

arrData(1, 0) = 0.1

arrData(2, 0) = 0.2

arrData(0, 1) = 0.3

arrData(1, 1) = 0.4

arrData(2, 1) = 0.5

'set up a range that represents teh data area

objRange = objSheet.Range("A2", Reflection.Missing.Value)

objRange = objRange.Resize(arrData.GetUpperBound(0) + 1, arrData.GetUpperBound(1) + 1)

'add the data in

objRange.Value = arrData

'set the window state to normal

objExcel.windowstate = -4143

'cleanup

objRange = Nothing

objSheet = Nothing

objSheets = Nothing

objBook = Nothing

objBooks = Nothing

objExcel = Nothing
--

Cheers,

elziko

"Paul Clement" <Us***********************@swspectrum.com> wrote in message news:lk********************************@4ax.com...
On Wed, 22 Oct 2003 14:38:45 +0100, "elziko" <el****@NOTSPAMMINGyahoo.co.uk> wrote:

I'm using late binding (I must) to automate Excel.

My code opens Excel after createing and poulating some sheets. My problem is
that when the user finally decides to close Excel its process is left
running until my application closes.

I have tried setting my Excel.Application object to Nothing.

I have tried to then fore the GC into action using:

GC.Collect()
GC.WaitForPendingFinalizers()

I have also tried to:

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objExcel)

which causes excel to crash (and offer to restart) when the user tries to
shut Excel down.

How can I get round this problem?

I the user is closing Excel, then try setting the UserControl property of the Application object to
True after your automation code has completed.


Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 20 '05 #4

P: n/a
Hi Elziko,

This question keeps cropping up but I haven't yet got a definitive
solution. (It's on the todo list but unfortunately not near the top)

It's possible that you'll have to do a ReleaseComObject on <each> of the
objects that you reference - for every one of them is an Excel COM object
which has its own reference count.

Setting objExcelThisAndThat = Nothing releases the object within .NET but
does nothing about the underlying COM object. This means that it waits for the
GC to do its job and call the object's Finalize. Did you know that the GC
needs to be called twice in order to get the Finalizers called - the first
time in, it simply makes a list of what is to be finalised, the second time
in, it actually <does> the finalising. Only <then> will the object do its
automatic ReleaseComObject.

The best people to ask may be the ones living here:
news://msnews.microsoft.com/microsof...mework.interop

Regards,
Fergus
Nov 20 '05 #5

P: n/a
* "elziko" <el****@NOTSPAMMINGyahoo.co.uk> scripsit:
My code opens Excel after createing and poulating some sheets. My problem is
that when the user finally decides to close Excel its process is left
running until my application closes.


Are you sure you close all workbooks/...?

--
Herfried K. Wagner
MVP VB Classic, VB.NET
<http://www.mvps.org/dotnet>
Nov 20 '05 #6

P: n/a
Elziko

try this

I put your code in a class (probably not necessary). What
worked I think, was to run the procedure in a new thread,
kill it when it finished, and then collect garbage.
You'll probably find you don't need all of this stuff but
at least it seems to work

dermot

::CODE::

Dim C As New Class1
Dim t As New Threading.Thread(AddressOf C.RunExcel)
t.Start()
t.Join() 'wait for sub to finish
GC.Collect()

'...in the class, we have
Sub RunExcel()

'YOUR CODE

'this line may not be necessary, at the end of the sub
Threading.Thread.CurrentThread.Abort()

End Sub
-----Original Message-----
I'm using late binding (I must) to automate Excel.

My code opens Excel after createing and poulating some sheets. My problem isthat when the user finally decides to close Excel its process is leftrunning until my application closes.

I have tried setting my Excel.Application object to Nothing.
I have tried to then fore the GC into action using:

GC.Collect()
GC.WaitForPendingFinalizers()

I have also tried to:

System.Runtime.InteropServices.Marshal.ReleaseCom Object (objExcel)
which causes excel to crash (and offer to restart) when the user tries toshut Excel down.

How can I get round this problem?

--

Cheers,

elziko
.

Nov 20 '05 #7

P: n/a
Thanks for your reply.
It's possible that you'll have to do a ReleaseComObject on <each> of the objects that you reference - for every one of them is an Excel COM object
which has its own reference count.
Yeah I have tried this too, but this also causes excel to crash when the
user tries to close it down.
Did you know that the GC
needs to be called twice in order to get the Finalizers called - the first


No I didnt! But I have now tried doing this and it has made no difference.

--

Cheers,

elziko
Nov 20 '05 #8

P: n/a
Thanks for your reply.
Are you sure you close all workbooks/...?


I dont close any workbooks. After the automation operation my application
hands control over to the user. He is responsible for what happens to Excel
and its data after that. I need to leave them open so the user can see the
data within the workbook.

--

Cheers,

elziko
Nov 20 '05 #9

P: n/a
> I put your code in a class (probably not necessary). What
worked I think, was to run the procedure in a new thread,
kill it when it finished, and then collect garbage.
You'll probably find you don't need all of this stuff but
at least it seems to work


Thats a good idea. And its does work although it really shouldnt be
necessary and I'd still like to get to the bottom of this. But I'm up and
running for now, thanks!

--

Cheers,

elziko
Nov 20 '05 #10

P: n/a
On Wed, 22 Oct 2003 15:46:41 +0100, "elziko" <el****@NOTSPAMMINGyahoo.co.uk> wrote:

> I the user is closing Excel, then try setting the UserControl property of
the Application object to
> True after your automation code has completed.

Thanks, but the Excel process still remains running after the user closes
Excel. Any other ideas.

Once the user has closed Excel manually your automation client no longer has control over the Excel
object references.

The only way I know of to terminate the Excel process at this point is to use API function calls:

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal
lpWindowName As String) As Int32
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg
As Int32, ByVal wParam As Int32, ByVal lParam As Int32) As Int32

Public Function TerminateExcel()

Dim ClassName As String
Dim WindowHandle As Int32
Dim ReturnVal As Int32
Const WM_QUIT = &H12

Do

ClassName = "XLMain"
WindowHandle = FindWindow(ClassName, Nothing)

If WindowHandle Then
ReturnVal = PostMessage(WindowHandle, WM_QUIT, 0, 0)
End If

Loop Until WindowHandle = 0

End Function

I suppose you could try GetObject as well but that may or may not work and you have no control over
which instance of Excel is retrieved.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #11

P: n/a
hey,

i had the same problem. The problem solved after i assign
all objects that refered to Excel Object to nothing
and then call CG.
and it is working...

goodluck
Lev.

-----Original Message-----
I'm using late binding (I must) to automate Excel.

My code opens Excel after createing and poulating some sheets. My problem isthat when the user finally decides to close Excel its process is leftrunning until my application closes.

I have tried setting my Excel.Application object to Nothing.
I have tried to then fore the GC into action using:

GC.Collect()
GC.WaitForPendingFinalizers()

I have also tried to:

System.Runtime.InteropServices.Marshal.ReleaseCom Object (objExcel)
which causes excel to crash (and offer to restart) when the user tries toshut Excel down.

How can I get round this problem?

--

Cheers,

elziko
.

Nov 20 '05 #12

P: n/a
> > It's possible that you'll have to do a ReleaseComObject on <each> of
the
objects that you reference - for every one of them is an Excel COM object
which has its own reference count.


Here is the code I have written that seems to do the trick:

System.Runtime.InteropServices.Marshal.ReleaseComO bject(objWS)
objWS = Nothing

objWB.Save()
objWB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objWB)
objWB = Nothing

objXL.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objXL)
objXL = Nothing

GC.Collect()

I believe that the GC would probably eventually clean up the process,
when it got good and ready, but by forcing it to collect, it cleans up
and the process goes away.
Nov 20 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.