0

I am having the same issue that many before me have had, and I have found several threads on the issue but my application of their fixes have not yielded any change.

I am reading an excel file and populating a DataGridView with it. Very simply the user can modify it and then save it. The problem, like the other threads, is that the program has such a tight grip on the file I can't overwrite it.

Among the many fixes offered, Garbage Collection was the most frequently mentioned, but my results are unchanged. I have tried disposing of the OLEDB connection, commands, adapters and datasets also without success.

I've been running through several different examples and tutorials to write this program and so if these fixes are required, I've obviously implemented them in the wrong spot.

Here is the reading block which happens on the form load (I've removed all my attempts to fix it to unclutter the code):

Private Sub Inventory_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Using MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\AllTrade\Inventory.xlsx';Extended Properties=Excel 8.0;")
        Using MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [InventorySheet$]", MyConnection)
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            InventoryGridView.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        End Using
    End Using
End Sub

Here is the saving block (This is the code that generates the "File in Use" error):

Private Sub UpdateInventory_Click(sender As Object, e As EventArgs) Handles UpdateInventory.Click

    Dim ExcelApp As New Excel.Application()
    ExcelApp.Application.Workbooks.Add(Type.Missing)

    For i As Integer = 0 To InventoryGridView.Rows.Count - 1
        Dim row As DataGridViewRow = InventoryGridView.Rows(i)
        For j As Integer = 0 To row.Cells.Count - 1
            ExcelApp.Cells(i + 1, j + 1) = row.Cells(j).Value
        Next
    Next

    ExcelApp.ActiveWorkbook.SaveAs("C:\Alltrade\Inventory.xlsx")
    ExcelApp.ActiveWorkbook.Saved = True
    ExcelApp.Quit()

End Sub

What's the trick to let go of the file? If the GC.Collect() and GC.WaitForPendingFinalizers() are required, where would they go?

I appreciate any help and apologize since I haven't been able to successfully implement the other answers from similar threads.

Mike Gauer
  • 39
  • 8
  • 1
    Quitting the `ExcelApp` is not enough. You must dispose of all the Excel objects. `ExcelApp.Dispose`. – Sam Axe Mar 15 '15 at 00:38
  • Thanks for the response, I didn't use the ExcelApp to read the file initially, and I'm fairly certain the problem is in the Reading of the file, as the error is generated when I try to save it. – Mike Gauer Mar 15 '15 at 16:00

2 Answers2

0

You need to marshal to release the COM object: ExcelApp...

 Private Sub ReleaseObject(ByVal obj As Object)
Try
    Dim intRel As Integer = 0
    Do
        intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    Loop While intRel > 0
Catch ex As Exception
    MsgBox("Error releasing object" & ex.ToString)
    obj = Nothing
Finally
    GC.Collect()
End Try
 End Sub

You can call this method like this at the end of your sub.

 ReleaseObject(ExcelApp)

You can get more here Excel application not quitting after calling quit

EDIT

I also noticed your connection string is wrong for the newer .xlxs file extention...

Normal ConnectionString : (work for xls files)

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\""

Office 2007 ConnectionString : (work for xlsx files)

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\""

Community
  • 1
  • 1
Trevor
  • 7,777
  • 6
  • 31
  • 50
  • Calling `.Dispose` on the excel objects will perform the same task. – Sam Axe Mar 15 '15 at 00:48
  • No it doesnt... I tried that before and it doesnt for COM objects... Well for the most part dispose works, but if using the Excel COM with the two dot rule, dispose doesnt work. See my link as Sid explains this. – Trevor Mar 15 '15 at 00:48
  • Hes using the 2dot.. "ExcelApp.ActiveWorkbook.SaveAs" for example as others as well. – Trevor Mar 15 '15 at 00:55
  • Thank you for your responses. I probably should have clarified that my 'File in use' error is generated at the Save point, which I'm assuming means the lock on the file happens from the read subroutine. As a measure of good practice, I'll add your code so it tidies up after Saving, but so far my issue remains. – Mike Gauer Mar 15 '15 at 01:00
  • Use "Using" statements to properly close out your connection and command. From what I can see your not disposing those and you need to... – Trevor Mar 15 '15 at 01:34
  • I had tried the Using statements previously with no change. I have reinstated them (and edited my code block up in the first post), am I using them incorrectly, pardon the pun? It did not change the result. As for the connection string, I've been trying to reformat your suggestion for a few minutes here so I didn't have to bug you for clarification, but the extra quotes at the end are breaking away the extended properties, and removing the quotes throws the error: Cannot find installable ISAM – Mike Gauer Mar 15 '15 at 01:57
  • I forgot my beginning quotes see edit sorry about that... the properties have to have the quotes around them as well, so youll have two sets of quotes... – Trevor Mar 15 '15 at 02:19
  • "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='c:\AllTrade\Inventory.xlsx';Extended Properties=\"Excel 12.0;HDR=YES;\"") Visual Studio is still throwing an error in the syntax. I've been playing around with it but can't get it accept it. The Excel 12.0;HDR=YES; is being treated as a partial expression instead of constrained in quotes – Mike Gauer Mar 15 '15 at 02:35
  • You have ticks in there in your datasource " ' " remove them they are not required. – Trevor Mar 15 '15 at 02:36
  • No change, the word "Excel" is underlined as the source of the error because it's being treated as outside the quotes. – Mike Gauer Mar 15 '15 at 02:42
  • Sorry doing this from phone you probably need to double quote the inside quotes.. for example: """ – Trevor Mar 15 '15 at 02:44
  • Finally got the connection string figured out! haha... didn't change my file issue though. – Mike Gauer Mar 15 '15 at 04:39
0

After much research and frustration, I found a solution.

The connection string of my OleDB connection needs the tidbit added: OLE DB Services = -4 which disables connection pooling. As it happens, the connection is not disposed of (which was the initial hypothesis) when the connection is closed, but instead dumps the connection back into the pool.

Another forum had suggested OLE DB Services = -2 which was supposed to do the same thing, but had no effect in my instance. It's possible it varies between versions but I haven't researched this to conclude that for certain (or someone typoed in one of the forums)

No further code was required in my program to dump the OleDB connection's hold on the file. MyConnection = Nothing and the line that drains the pool followed by a GC.Collect were also recommended but did not resolve my symptoms.

Thanks everyone for the input

Mike Gauer
  • 39
  • 8