Close Excel Workbook If Test for Open Returns False

Question

I've constructed a VB.Net application which loads data into an Excel spreadsheet. The application works fine, but I've added a functionality to test whether the workbook is Open, and if so, the application terminates. Otherwise, if the workbook Is Not Open, then the user can proceed to fill information in the application. My issue is that when is when the worksheet is not open, my code blows up due to the workbook being "somehow opened." I need to close any processes, then proceed. Here's my code for checking if the workbook is open or not:

1st, my module, which sets up the a Boolean check:

Public Module ExcelCheck
Public Function Test(ByRef sName As String) As Boolean

Dim fs As FileStream

    Try
        fs = File.Open(sName, FileMode.Open, FileAccess.Read, FileShare.None)
        Test = False
    Catch ex As Exception
        Test = True
    End Try

End Function
End Module

Then my handler for a button on the form that does the check:

Private Sub btnOpenFileCheck_Click(sender As Object, e As EventArgs) Handles btnOpenFileCheck.Click

    'Evaluate if the workbook is being used: 
    Dim bExist As Boolean

    bExist = Test("\\netshareA\c$\Users\Pete\Desktop\TestUnits\Machines.xls")

    If bExist = True Then
        MessageBox.Show("The file is open... Please try again later.", "EXCEL FILE IN USE: Abort", MessageBoxButtons.OK)

        Me.Close()

    Else bExist = False
        MessageBox.Show("The file is NOT open... You may proceed...", "EXCEL FILE NOT OPEN", MessageBoxButtons.OK)

        Dim xlOpenItem As New Excel.Application
        Dim xlOpenWB As Excel.Workbook = xlOpenItem.Workbooks.Open("\\netshareA\c$\Users\Pete\Desktop\TestUnits\Machines")

        xlOpenWB.Close(SaveChanges:=False, Filename:="\\netshareA\c$\Users\Pete\Desktop\TestUnits\Machines.xls", RouteWorkbook:=False)

        txtCPUSerial.Focus()

    End If
End Sub

What happens when the book isn't open is the proper dialog to continue runs via the illustration:

But then an Excel dialog appears saying the following:

'\\netshareA\c$\Users\Pete\Desktop\TestUnits\Machines.xls' is currently in use. Try again later.

Then, it finally blows up and I have the line of referenced:

Dim xlOpenWB As Excel.Workbook = xlOpenItem.Workbooks.Open("\\netshareA\c$\Users\Pete\Desktop\TestUnits\Machines")

My logic is that I need to have an open instance of an Excel object, then close that instance in order to terminate any inadvertent running process. I actually open the workbook in another submit handler, with the Excel objects and variables set well, but that's not my issue. How can I smoothly make sure the workbook object is closed here as as to not throw an exception that it isn't?

Answer 1

After much tinkering around, I've found out exactly what my problem was - I didn't close out the opened file stream:

Public Module ExcelCheck
Public Function Test(ByRef sName As String) As Boolean

Dim fs As FileStream

Try
    fs = File.Open(sName, FileMode.Open, FileAccess.Read, FileShare.None)
    Test = False

    fs.Close() 'This closes out the initially opened file stream for checking.

Catch ex As Exception
    Test = True
End Try

End Function
End Module

I eventually came back to the module and wondered what happens if I just close out and use fs.Close() and it did the trick. No more blow ups! Hope this helps someone else whom might struggle with a similar file stream issue.