473,288 Members | 1,743 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Excel Automation

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
12 3182
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
> 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
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
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
* "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
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
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
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
> 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
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
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
> > 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Ange T | last post by:
Hi there, I'm having pain with the VB behind an Access form. The form is used to create reports in Excel based on the details entered in the form. This has always worked without error on my...
2
by: jeffgeorge | last post by:
I'm currently exporting a form to Excel. Because there are controls and totals in the header, I first have a button for users to convert to a datasheet. Then I use the automated quick office...
7
by: taylor.bryant | last post by:
I am running: Win XP SP2 Excel 2002, Access 2002 (Office XP SP3) Using Visual Basic (not VB.NET) At one point (prior to XP SP2?!? - I can't pin it down), this did not happen and I was easily...
1
by: cybertof | last post by:
Hello, Is there a way to connect (through automation) a c# application to a running Excel 2003 instance on a specific workbook ? In the past, i used to use GetObject(...) function in VB6. ...
17
by: Mansi | last post by:
I need to do some research on how to use excel automation from c#. Does anyone know of any good books related to this subject? Thanks. Mansi
12
by: D. Shane Fowlkes | last post by:
This most likely belongs in another forum but I thought I'd start here. I have a COM Object written in VB6. The DLL will access MS Excel and use it's Object Library to write a customized report...
3
by: Carlos Magalhaes | last post by:
Hey All, I am doing some excel automation using the excel COM. I can do most of the functions and its working well until I come across a formula. I can run a formula and insert the formula...
3
by: Mitchell Vincent | last post by:
Does anyone have some good examples of Excel automation with (VB).NET? I have some Excel spreadsheets that a customer needs parsed out but I've never tried to use Excel programatically before! ...
6
by: a.theil | last post by:
Please help! I need a simple excel automation, just 2 write some files into excel. I do: Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As...
1
by: Troy | last post by:
I have VB.Net code that opens Excel files and imports data from them. It works fine for all versions up to 2003 and has for over a year. We have users updating to Office 2003 and the Excel...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.