Excel.exe ALive with OldDBconnection when Spreadsheet Already Ope

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using an oldDBconnection to do a sql insert and update to a spreadsheet.

If the Speadsheet is open in excel when the .net program updates the
spreadsheet then excel.exe lingers in task manager.

Can anyone suggest what is the fix? Code works fine if spreadsheet is not
open.
Thanks very fustrating excel.exe lingering.

Here is code snippet:

Dim conn As OleDbConnection
Try
conn = New OleDbConnection(szConnect)

conn.Open()

cmd = New OleDbCommand

cmd.Connection = conn
' test if there
szSQL = "Select * from " & strSheet & " WHERE " &
strWhereCol & "='" & strSymbol & "'"
cmd.CommandText = szSQL
oledbReader = cmd.ExecuteReader()
If (oledbReader.HasRows) Then
' can do UPDATE
szSQL = "UPDATE " & strSheet & " SET " & strFieldName &
"='" & strDateIn & "' WHERE " & _
strWhereCol & "='" & strSymbol & "'"
Else
' try and insert
szSQL = "INSERT INTO " & strSheet & " (" & strWhereCol &
"," & strFieldName & ") VALUES('" & strSymbol & _
"','" & strDateIn & "')"
End If
' now close datareader
oledbReader.Close()
cmd.CommandText = szSQL
cmd.ExecuteNonQuery()
cmd.Connection.Close()

conn.Close()
conn.Dispose()
cmd.Dispose()
 
Neil said:
I am using an oldDBconnection to do a sql insert and update to a spreadsheet.

If the Speadsheet is open in excel when the .net program updates the
spreadsheet then excel.exe lingers in task manager.

Can anyone suggest what is the fix? Code works fine if spreadsheet is not
open.
Thanks very fustrating excel.exe lingering.

Excel likes to linger on my computer too, but it never causes me trouble. I never quite
figured how to reliably tell it to quit.

What trouble does it cause you?
 
Alan:

when it lingers if someone closes out excel and all spreadsheets, then
reloads excel
with a new spreadsheet it hangs Excel and nothing opens.

Then have to go into task manager and kill excel.exe task. My end users
can't do this.

Any solutions would be appreciated. Only one I can think of is forcing the
particular spreadhseet I SQL write to to close it firrst. But I don't know how
to close a specific spreadhseet programmaticaly.

Microsoft Knowledge base reports the problem as something with Interop but
no good solution .

Any help appreciated.
 
Neil said:
Alan:

when it lingers if someone closes out excel and all spreadsheets, then
reloads excel
with a new spreadsheet it hangs Excel and nothing opens.

Then have to go into task manager and kill excel.exe task. My end users
can't do this.

Any solutions would be appreciated. Only one I can think of is forcing the
particular spreadhseet I SQL write to to close it firrst. But I don't know how
to close a specific spreadhseet programmaticaly.

Here's an unmanaged C++ version:

Workbooks objBooks = objApp.GetWorkbooks();

COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

_Workbook objBook;

objBook = objBooks.Open(fileName,

VOptional, /*ReadOnly=*/ vValA, VOptional, VOptional,

VOptional, VOptional, VOptional, VOptional,

VOptional, VOptional, VOptional, VOptional);

// do stuff...

// close

objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);


If that doesn't work, you'll have to ask someone else. Good luck!
 
Back
Top