By user1222679


2012-02-21 06:19:48 8 Comments

In VBA, I opened an MS Excel file named "myWork.XL" programmatically.

Now I would like a code that can tell me about its status - whether it is open or not. I.e. something like IsWorkBookOpened("myWork.XL) ?

7 comments

@Ian 2013-11-14 16:33:00

Checkout this function

'********************************************************************************************************************************************************************************
'Function Name                     : IsWorkBookOpen(ByVal OWB As String)
'Function Description             : Function to check whether specified workbook is open
'Data Parameters                  : OWB:- Specify name or path to the workbook. eg: "Book1.xlsx" or "C:\Users\Kannan.S\Desktop\Book1.xlsm"

'********************************************************************************************************************************************************************************
Function IsWorkBookOpen(ByVal OWB As String) As Boolean
    IsWorkBookOpen = False
    Dim WB As Excel.Workbook
    Dim WBName As String
    Dim WBPath As String
    Err.Clear
    On Error Resume Next
    OWBArray = Split(OWB, Application.PathSeparator)
    Set WB = Application.Workbooks(OWBArray(UBound(OWBArray)))
    WBName = OWBArray(UBound(OWBArray))
    WBPath = WB.Path & Application.PathSeparator & WBName
    If Not WB Is Nothing Then
        If UBound(OWBArray) > 0 Then
            If LCase(WBPath) = LCase(OWB) Then IsWorkBookOpen = True
        Else
            IsWorkBookOpen = True
        End If
    End If
    Err.Clear
End Function

@brettdj 2014-01-15 10:53:34

This will capture if the workbook is open in the current instance on the local machine - it wont capture whether the workbook is open in another local instance, or by another user elsewhere.

@Winand 2015-10-15 11:11:16

I think WB.Path & "\" & WBName is WB.FullName

@Mor Sagmon 2017-06-30 08:06:30

I'd also add Set WB = Nothing before exiting the function

@Bhanu pratap 2018-08-17 14:26:50

now working with filename only...

@Siddharth Rout 2012-02-21 07:48:44

Try this:

Option Explicit

Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("C:\myWork.xlsx")

    If Ret = True Then
        MsgBox "File is open"
    Else
        MsgBox "File is Closed"
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

@brettdj 2012-02-21 09:28:19

+1 I've used this method for sometime to check files on a newtwork drive accessible by other users. I think the code was originally posted on a msft site.

@Charles Williams 2012-02-21 20:34:03

Personally I would feel very uncomfortable using primitive file IO to attempt a file read on an open Excel workbook when IMHO there are better alternatives: but maybe it works?

@Siddharth Rout 2012-02-22 01:43:10

@Charles Williams: Yes, it may be primitive but it is still a good code with no disadvantages. At least none that I know of. :) Try it maybe you will like it?

@Charles Williams 2012-02-22 08:52:58

I am sure it works, but what do you see as the disadvantages of the simpler more Excel-friendly code? (opening the workbook using Workbooks.Open and checking Workbook.Readonly)

@brettdj 2012-03-10 06:50:46

@CharlesWilliams Fair point. Although in my case when I tried something similar the time overhead of actually opening a large model hosted on a overseas server was around 2-3 minutes. Which gave a "grrr" moment when it opened readonly, whereas Sid's function above gave an immediate response. FWIW Bob Phillips listed a similar function at vbaexpress , a more advanced version waiting for the book to be closed elsewhere from Chip Pearson

@Chuck The Nerd 2014-03-21 21:15:02

@SiddharthRout There is one disadvantage that just bit me. I just tested that above code when saved as "C:\myWork.xlsm", closed, then opened as read-only and run will pop MsgBox "File is Closed". This is incorrect since ThisWorkbook is open and running Sample(). Seems like fix is to include code that also checks each Application.Workbooks().name

@Nigel Heffernan 2015-07-27 09:48:26

@SiddharthRout - have you tried GetObject( [MyfilePath], Class:="Excel.Workbook" ) ? This seems to work, and I've tested it with multiple instances of Excel.exe - however, I can think of several failure modes and I'm reluctant to post an answer without a lot more testing.

@Siddharth Rout 2015-07-27 09:52:42

@Nile: If you can think of several failure modes then it is not a reliable solution :)

@AER 2016-08-18 07:35:59

Doesn't work I'm afraid. I've copy pasted it verbatim. Which version of VBA is this?

@Siddharth Rout 2016-08-18 10:50:05

@AER: It worked then and it works now as well :) Are you sure you are passing the correct file path and name?

@AER 2016-08-19 04:33:23

@SiddharthRout: Hmm, OK. Yes, I both copy-pasted and typed it in. Does it require any admin rights?

@Kirk Kittell 2018-06-22 17:14:05

It seems like IsWorkBookOpen() will go to Case 0 (IsWorkBookOpen = False) on a shared workbook whether the workbook is open or closed.

@user2267971 2014-03-10 19:39:41

I would go with this:

Public Function FileInUse(sFileName) As Boolean
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function

as sFileName you have to provide direct path to the file for example:

Sub Test_Sub()
    myFilePath = "C:\Users\UserName\Desktop\example.xlsx"
    If FileInUse(myFilePath) Then
        MsgBox "File is Opened"
    Else
        MsgBox "File is Closed"
    End If
End Sub

@Derek Johnson 2013-08-09 07:19:22

What if you want to check without creating another Excel instance?

For example, I have a Word macro (which is run repeatedly) that needs to extract data from an Excel spreadsheet. If the spreadsheet is already open in an existing Excel instance, I would prefer not to create a new instance.

I found a great answer here that I built on: http://www.dbforums.com/microsoft-access/1022678-how-check-wether-excel-workbook-already-open-not-search-value.html

Thanks to MikeTheBike and kirankarnati

Function WorkbookOpen(strWorkBookName As String) As Boolean
    'Returns TRUE if the workbook is open
    Dim oXL As Excel.Application
    Dim oBk As Workbook

    On Error Resume Next
    Set oXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        'Excel is NOT open, so the workbook cannot be open
        Err.Clear
        WorkbookOpen = False
    Else
        'Excel is open, check if workbook is open
        Set oBk = oXL.Workbooks(strWorkBookName)
        If oBk Is Nothing Then
            WorkbookOpen = False
        Else
            WorkbookOpen = True
            Set oBk = Nothing
        End If
    End If
    Set oXL = Nothing
End Function

Sub testWorkbookOpen()
    Dim strBookName As String
    strBookName = "myWork.xls"
    If WorkbookOpen(strBookName) Then
        msgbox strBookName & " is open", vbOKOnly + vbInformation
    Else
        msgbox strBookName & " is NOT open", vbOKOnly + vbExclamation
    End If
End Sub

@Bulki 2013-07-09 14:26:43

This one is a bit easier to understand:

Dim location As String
Dim wbk As Workbook

location = "c:\excel.xls"

Set wbk = Workbooks.Open(location)

'Check to see if file is already open
If wbk.ReadOnly Then
  ActiveWorkbook.Close
    MsgBox "Cannot update the excelsheet, someone currently using file. Please try again later."
    Exit Sub
End If

@Linga 2018-03-14 12:33:25

Short and sweet :)

@Dick Kusleika 2012-02-21 17:18:57

For my applications, I generally want to work with a workbook rather than just determine if it's open. For that case, I prefer to skip the Boolean function and just return the workbook.

Sub test()

    Dim wb As Workbook

    Set wb = GetWorkbook("C:\Users\dick\Dropbox\Excel\Hoops.xls")

    If Not wb Is Nothing Then
        Debug.Print wb.Name
    End If

End Sub

Public Function GetWorkbook(ByVal sFullName As String) As Workbook

    Dim sFile As String
    Dim wbReturn As Workbook

    sFile = Dir(sFullName)

    On Error Resume Next
        Set wbReturn = Workbooks(sFile)

        If wbReturn Is Nothing Then
            Set wbReturn = Workbooks.Open(sFullName)
        End If
    On Error GoTo 0

    Set GetWorkbook = wbReturn

End Function

@Charles Williams 2012-02-21 20:30:40

I agree thats usually what is wanted: if you want to check for the book being already open in another Excel instance you can check if its been opened readonly

@motobói 2014-06-10 16:01:56

This giveme out of bounds error on Workbooks(sFile)

@Dick Kusleika 2014-06-10 18:39:30

You must not have On Error Resume Next in the code or you have Break on All Errors set under Tools - Options in the VBE.

@Lowpar 2017-12-05 08:29:22

This version works better for me, the version above seems to not detect workbooks open in read only...

@André Chalella 2017-12-18 00:09:29

I used to use this, but these days I've been getting a lot of Automation errors in Excel 2017 when the concerned workbook had been closed moments prior to running the macro. The solution was to forgo On Error Resume Next (because wbReturn was not Nothing, but contained an error) and write real error handling. See: pastebin.com/u1LLgPa1

@Charles Williams 2012-02-21 08:44:39

If its open it will be in the Workbooks collection:

Function BookOpen(strBookName As String) As Boolean
    Dim oBk As Workbook
    On Error Resume Next
    Set oBk = Workbooks(strBookName)
    On Error GoTo 0
    If oBk Is Nothing Then
        BookOpen = False
    Else
        BookOpen = True
    End If
End Function

Sub testbook()
    Dim strBookName As String
    strBookName = "myWork.xls"
    If BookOpen(strBookName) Then
        MsgBox strBookName & " is open", vbOKOnly + vbInformation
    Else
        MsgBox strBookName & " is NOT open", vbOKOnly + vbExclamation
    End If
End Sub

@Siddharth Rout 2012-02-21 09:04:17

Charles, I already thought of this method. The main drawback on this method is that if the workbook is opened in a different Excel instance then you will always get the value as false :) the alternative would be to add code to loop through all Excel instances and then use your code. Ultimately I realized that I was writing more code and hence I used an alternative approach. Sid

@Charles Williams 2012-02-21 20:29:02

If you want to check for the book being open in another Excel instance (presumably because you won't be able to save it or edit it) why not just check if its Readonly after opening it (If oBk.Readonly ...)

@glh 2013-04-19 10:46:13

What if it's shared?

Related Questions

Sponsored Content

7 Answered Questions

[SOLVED] Setting mime type for excel document

2 Answered Questions

Open Excel OLEObject in a new Excel instance

  • 2019-03-16 20:47:14
  • i_caster
  • 53 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba

25 Answered Questions

[SOLVED] Is it possible to force Excel recognize UTF-8 CSV files automatically?

  • 2011-05-14 13:53:39
  • Lyubomyr Shaydariv
  • 433107 View
  • 379 Score
  • 25 Answer
  • Tags:   excel csv utf-8

22 Answered Questions

[SOLVED] Microsoft Excel mangles Diacritics in .csv files?

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 998116 View
  • 1732 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

34 Answered Questions

[SOLVED] Stop Excel from automatically converting certain text values to dates

  • 2008-10-02 23:30:43
  • user16324
  • 439133 View
  • 474 Score
  • 34 Answer
  • Tags:   excel csv import

1 Answered Questions

13 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • BiGXERO
  • 235038 View
  • 475 Score
  • 13 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Excel Vba not opening correct workbooks?

3 Answered Questions

[SOLVED] What is correct content-type for excel files?

  • 2010-05-30 03:54:47
  • taw
  • 460987 View
  • 416 Score
  • 3 Answer
  • Tags:   excel mime-types

Sponsored Content