Microsoft.Office.Interop.Excel leaves processes in the task list

I’ve migrated some code from vb2005, via vb 2008 to vb 2010.  I’ve changed the program quite a lot but the Excel class has not been changed.  Now when I run the program one or more excel.exe’s are left in the tasklist.  This used to happen rarely in vb 2005, the vb 2008 version was never run in anger, and it happens all the time in vb 2010.  I’ve read lots of articles on the web saying I must dispose of all the objects, well maybe I should, but …
( the logic works, why should I have to worry, all child objects should be cleared by the xl=nothing at the end)

So it’s poor coding, or perhaps a poor compiler take your choice.

I just need it to work properly – to not leave excel.exe behind.

The key parts or the following code are lifted from http://www.craigmurphy.com/blog/?p=82 comment #11 by Mahdi and converted into vb:

Public Class ExcelAccess

Dim xl As Microsoft.Office.Interop.Excel.Application
Public xlHashtable As New Hashtable()      ‘ Instance has table for existing execl
‘ requires rescouce from COM (add reference): Microsoft Office 11.0 Object Library 2.3, Microsoft Excel 11.0 Object Library
Public Sub New()
CheckExcellProcesses()          ‘ Store existing processes in the Hastable

End Sub

Public Sub ExcelNew()

xl = CType(CreateObject(“excel.Application”), Excel.Application)
xl.Workbooks.Add()
xl.Visible = True

End Sub

Public Sub ExcelQuit()
xl.Application.Quit()
xl = Nothing
KillExcel()
‘Unload(Me)
End Sub

‘ Converted from c

Private Sub CheckExcellProcesses()
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

Dim iCount As Integer = 0

For Each ExcelProcess As Process In AllProcesses
xlHashtable.Add(ExcelProcess.Id, iCount)
iCount = iCount + 1
Next

End Sub
Public Sub KillExcel()

‘ Remove any excel’s started since this process atarted
Dim AllProcesses As Process()
AllProcesses = Process.GetProcessesByName(“excel”)

‘ check to kill the right process
For Each ExcelProcess As Process In AllProcesses

If (xlHashtable.ContainsKey(ExcelProcess.Id) = False) Then
ExcelProcess.Kill()
End If
Next
AllProcesses = Nothing

End Sub

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

msdn seems to have a reasonable description of the problem here.  However my SQL Server log does not contain any entries for the time of the failure.

The last message in the latest SQL ErrorLog file is at 04:29:58.48

My application reports the connection status as open at 05:21:37.781

The failure occurs at 05:21:37.875 – less than 0.1 seconds later!

There are no messages in the eventlog in this time period.

How to scroll a textbox programatically as data is added

This should be easy, but after some experimentation it appears at least this sequence is required:

textbox1.Text = textbox1.Text & sLine & Environment.NewLine  ‘ Add the data

textbox1.Focus() ‘ claim the focus

textbox1.SelectionStart = Len(textbox1.Text) – 1 ‘ set the Caret

textbox1.ScrollToCaret() ‘ move the view of the text box

textbox1.Refresh() ‘ display the view of the textbox.

This works while the application itself has focus.

How to get an empty table from a sql query in VB 2005 (.net) without timeouts

Its simple – write a select that guarantees the return of nothing.  You will need to know your data.  In my case this is for adding rows to a log table.  The rows are timestamped when they are entered, so I had:

SELECT [Time], [Message] FROM [LogTable]

where [Time] > {fn Now() }

It worked.

Then it began to time out.

Every time the SELECT was run a table scan occurred.  The log now has just over 500,000 rows.

I removed some to a history table, down to 100,000, but still no good.

The execution plan says table scan, so that is the obvious cause.  I soon discounted indexes, the table is rarely read, and I’m prepared for the table scan when it is, as the select is likely to include [Message] Like ‘%something%’

Then I tried

SELECT [Time], [Message] FROM [LogTable]

where 1 = 2

the execution plan says ‘constant scan’, and the query analyser reports 00:00:00 as the execution time.

FIXED!

My.User.Name returns an empty string

I’m not sure when or why this happened.  It MAY be related to this application being a console application.

The alternative is:

Console.WriteLine(“Windows ID:” & System.Security.Principal.WindowsIdentity.GetCurrent.Name)

from a post here:

http://bytes.com/topic/visual-basic-net/answers/585547-my-user-name-returning-empty-xp-pro-sp2

‘Public Property Left() or Right() As Integer’ has no parameters and its return type cannot be indexed

The message appears against x = Left(a,2) – all of them.

The problem goes away if I comment out the line:
Imports System.Collections.ObjectModel
but I need that in order to define a variable as
ReadOnlyCollection(Of String)

There are some sites that suggest the Left is deprecated as a function, but this does not appear to be the case. (http://msdn.microsoft.com/en-us/library/y050k1wb(VS.80).aspx)

Resolution

This appears to be a poor piece of decision making by the compiler.  Left can be qualified as strings.left, in which case the interpretation is explicit and correct.

e.g.

x = Strings.Left(a,2)