Answered Truly Closing Excel...

[JaedenRuiner]

edit:
here's the link for the answer,
MSDN Support for Releasing Com Object from .Net

Okay
i put the here because it sort of involves data access, and I figure you'll move it, but I couldn't find an "Office Automation" or Interop forum, so I dropped it here.

Anyway, So here is the eventual goal:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\db\rexam.xls', [Sheet1$])

Now simply enough, I can set this into a TableAdapter.SelectCommand() and then Fill a DataSet, and now my life is happy.

However (and yes you knew there was a however, coming) if you notice the format of that command (the OPENROWSET, or the OPENDATASOURCE) the final parameter is the "WorkSheet Name". It is appropriate to remember that the "$" is necessary to tell that it is the Actual worksheet, (i know without the $ Sheet1 means something else but i forget and it is irrelevant). The issue is: What if you don't know the "name" of the worksheet??

You get all the way through, knowing the filename and everything only to be thwarted by the fact whoever made that excel file could have renamed their worksheets anything. So One can revert to Interop (woohoo) to open up the excel workbook, and get a list of all the worksheets within the the file and all is hunky dory...except...it isn't.

Check This:

'This is partial code of a class
      Private _app As Excel.Application
      Private _wb As Excel.Workbook
      Private _wks As Excel.Worksheet

      Public Sub Load(ByVal filename As String)
         If IO.File.Exists(filename) Then
            Try
               _app = CreateObject("Excel.Application")
               _app.Visible = False
               _wb = _app.Workbooks.Open(filename, ReadOnly:=True)
               If _wb IsNot Nothing Then
                  _wks = _wb.Worksheets(1)
               End If
            Catch
          
     _app = Nothing
               _wb = Nothing
               _wks = Nothing
            End Try
         End If
      End Sub

So not to difficult, and once i have what i'm looking for, I can do this:

_wks = nothing
_wb.Close(False)  'though for readonly the "false" is unnecessary, but i might not always have it opened readonly
_app.Quit()

And then now that I have the worksheet name (we'll just assume my connectivity actually grabbed the name, even though this class was used for something else) I go to run my OPENROWSET query...and OLEDB has a Seizure.

With one of the many tools at my disposal, (Unlocker 1.8.7) I can unlock file handles from files windows still thinks are open, and lo and behold, the XLS file I am trying to open is still open under Excel...the very same Excel Application that I do believe i just execute. _app.Quit(). Well I guess they don't make Quit() functions like they used to, , and in the end I need to find a way to truly order Excel (through my automation) to officially, finally, terminally, and in no uncertain terms, Quit so that once I grab my worksheet names and generate my rowset queries, the once open file that I assumed was closed does not block my oledb request.

Thanks