none
Check whether an excel file is already open using VB.NET

    Question

  • Hi there,

    Am read the data from excel file and proceed with the VB.Net code. I would like to know whether that excel file is already open using VB.Net code. Suppose am having the file "Sample.CSV" if already opened a message will indicate to the user. Please let me know if anyone have the solution.

    Thanks in advance,

    • Changed type Karthik S P Monday, May 21, 2012 6:52 AM
    Friday, May 18, 2012 5:18 AM

Answers

  • Hi Karthik,

    Welcome to the MSDN forum!

    Please refer to the following code:

        ' Imports System.IO
        Sub Main()
            
            Dim bExist As Boolean
            bExist = TestExcel("Test.xlsx")
            If bExist = True Then
                Console.WriteLine("The file is already open")
            Else
                Console.WriteLine("The file isn't open")
            End If
            Console.ReadLine()
    
        End Sub
    
        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

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Karthik S P Monday, May 21, 2012 6:52 AM
    Monday, May 21, 2012 5:06 AM
    Moderator

All replies

  • Hi Karthik,

    Welcome to the MSDN forum!

    Please refer to the following code:

        ' Imports System.IO
        Sub Main()
            
            Dim bExist As Boolean
            bExist = TestExcel("Test.xlsx")
            If bExist = True Then
                Console.WriteLine("The file is already open")
            Else
                Console.WriteLine("The file isn't open")
            End If
            Console.ReadLine()
    
        End Sub
    
        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

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Karthik S P Monday, May 21, 2012 6:52 AM
    Monday, May 21, 2012 5:06 AM
    Moderator
  • Thank you Yoyo Jiang. The code works....
    Monday, May 21, 2012 6:32 AM
  • Another thank you from me!
    Wednesday, October 31, 2012 10:38 AM
  • Hi there, I have been looking for something along these lines for a while now and thought you were my saviour ... however ... my problem is that it's a Word Document, not an Excel document (though I don't think that is relevant), and I will be editing it, either as a new file, a re-opened file, or an already open file, but if I add your logic, the file is always locked !!!

    This is my code, with yours commented out ... this works if the file is NOT open, if it is open it crashes, hence my problem & need for a solution (I have had other options sent my way by a very helpful Cindy Meister, but they are so complicated compared to this option ... if it works !!!).

            'Dim myFileOpen As Boolean
    
            'myFileOpen = IsFileOpen(myNewsLetter)
    
            'If myFileOpen = True Then
            'MsgBox("The file is already open")
            'Else
            Dim MSWord As New Word.Application
            Dim MSDoc As New Word.Document
            MSDoc = MSWord.Documents.Open(myNewsLetter)
            MSWord.WindowState = Word.WdWindowState.wdWindowStateNormal
            MSWord.Visible = True
            MSWord.Activate()
            'End If
    
    ====================================================================
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Me.Close()
        End Sub
    
        'Function IsFileOpen(ByRef sName As String) As Boolean
        '    Dim fs As FileStream
        '    Try
        '        fs = File.Open(sName, FileMode.Open, FileAccess.Read, FileShare.None)
        '        IsFileOpen = False
        '    Catch ex As Exception
        '        IsFileOpen = True
        '    End Try
        'End Function
    

    However, as soon as I uncomment your code, something, I assume it is the "fs = File.Open" command seems to lock the Word Document in the vshost.exe process.

    Am I doing something wrong ? Is it an adjustable option ?

    Friday, January 25, 2013 2:24 PM
  • I would change the Test Function because it has logical issue.
    This function will always return false as long it is called more than once for the same file...
    I would rather go with something like this:

        Function isFileOpen(ByRef sName As String) As Boolean
            Dim blnRetVal As Boolean = False
            Dim fs As FileStream

            Try
                fs = File.Open(sName, FileMode.Open, FileAccess.Read, FileShare.None)
            Catch ex As Exception
                blnRetVal = True
            Finally
                If Not IsNothing(fs) Then : fs.Close() : End If
            End Try

            Return blnRetVal
        End Function

    Thursday, May 08, 2014 6:55 PM
  • Excellent. That's better.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 13, 2014 7:22 PM
    Moderator