0

In VB.NET I'm reading an Excel Spreadsheet like so:

Dim xlApp As Excel.Application = New Excel.Application
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(Server.MapPath(SavePath & sFilename))
Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Sheets(1)
Dim eRange As Excel.Range = xlWorkSheet.Range("C3:C" & xlApp.Rows.End(Excel.XlDirection.xlDown).Row)
Dim bottomRange As Integer = xlApp.Rows.End(Excel.XlDirection.xlDown).Row
...

After I open it up and read some data I want to close it and make it possible for someone to delete it MANUALLY(clicking on the file and pressing delete) afterwards, so after looking around I found this sub that is supposed to release the locks on the objects that I create:

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

But whenever I create a spreadsheet and close my program and then try to delete the file MANUALLY, not in my program, I get the error:

The action can't be completed because the file is open in Excel 2016. Choose the file and try again.

This error even persists after I have closed my program. Can someone please help me out in figuring out how to release these locks Excel has on the files? I should also note that I pass xlApp to the sub.

  • You need to close the workbook and exit the Excel app. Call `xlWorkBook.Close()` then `xlApp.Quit()`. – 41686d6564 stands w. Palestine May 03 '18 at 20:10
  • no database stuff, this is just simply reading in the data from an uploaded spreadsheet, putting it in a different spreadsheet and then closing them both down. –  May 03 '18 at 20:11
  • I just tried xlWorkBook.close() and xlApp.Quit() and it still doesn't work –  May 03 '18 at 20:14
  • @Jacob H can you share what you did in your situation that worked for you instead of the releaseobject stuff? –  May 03 '18 at 20:16
  • Ahmed has given you the solution I think in calling Close() and Quit(). – Jacob H May 03 '18 at 20:21
  • I tried them both and it still doesn't work. Here's exactly how I call them: xlWorkBook.Close() xlApp.Quit() –  May 03 '18 at 20:23
  • @BrendonDavies Here's a blog explaining the whole "dont use marshal.releasecomobject" :. https://blogs.msdn.microsoft.com/visualstudio/2010/03/01/marshal-releasecomobject-considered-dangerous/ Doesn't solve your issue I know. Maybe you can post some more code? Have you tried killing any open Excel processes manually before trying your updated code? – Jacob H May 03 '18 at 20:43
  • Possible duplicate of [Clean up Excel Interop Objects with IDisposable](https://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable) – 41686d6564 stands w. Palestine May 03 '18 at 20:51
  • @Ahmed I rewrote some of the code I have so that it would reflect the answer you gave about the GC and the WaitForPendingFinalizers and it ended up working. I thought that the differences between how I had it and how you had it were insignificant but they turned out to make all the difference. If it was put as an answer for this question I would upvote it. Thanks. –  May 04 '18 at 16:38
  • 1
    @BrendonDavies This is why I asked you yesterday if you used it exactly how I had explained. Anyways, I'm glad I was able to help. I would rather you upvote Hans's answer if you think it's helpful. No need to post a duplicate answer. – 41686d6564 stands w. Palestine May 04 '18 at 17:10

0 Answers0