How will I know if my worksheet is also opened to another workbook

Question

My Goal is to pop-up an message box "The file is opened to another workbook, please close it"

The problem is I am trying to detect if the filename sheet is opened to another workbook.

I tried to code it but I will only detect if the filename is open to the workbook that I used.

Public Sub MainDelete()    
    xRet = IsWorkBookOpen(currentName & ".xlsx")
    If t_int_fc.FolderExists(SuperFinalmyPath & "\検査資料（PH→DTJP）\塗りつぶし結果\PH塗り潰し結果\セルフ結果\Tool②_Output(Delete)\") = True Then
        If xRet Then
            Call Warnings(7)
            CheckOpen = True
        Else                      
            CheckOpen = False
        End If
    Else
       'Do nothing
    End If  
End Sub


Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function


Public Sub Warnings(Num As Integer)
    Select Case Num
        Case 1
            MsgBox "入力 Section is not existing"
        Case 2
            MsgBox "理論 Section is not existing"
        Case 3
            MsgBox "Incorrect Placement of 入力値 Section"
        Case 4
            MsgBox "Incorrect Placement of 理論値 Section"
        Case 5
            MsgBox "No Target(対象) Items"
        Case 6
            MsgBox "Inspection sheet must be located in 「検査結果」folder"
        Case 7
            MsgBox "Generated file is already open! Please close it first."

    End Select
End Sub

Macro code to check whether a file is already open https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open — Hiten004, Mar 25 '19 at 13:31
What does the phrase "to another workbook" mean? Do you think of a situation in which multiple Excel processes run (multiple Application objects exist)? — z32a7ul, Mar 25 '19 at 13:35
You could look at the following to check instances of Excel being opened https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f — Nathan_Sav, Mar 25 '19 at 13:37
https://stackoverflow.com/questions/55338638/how-will-i-know-if-my-worksheet-is-also-opened-to-another-workbook#55338638 — , Mar 25 '19 at 13:41
I want to detect if a specific file is open in ther microsoft excel workbook, not on the active work book. — marubindesu, Mar 25 '19 at 13:42
I've used the FileInUse function from https://www.mrexcel.com/forum/excel-questions/737467-vba-if-workbook-read-only-post3625779.html#post3625779 — Mistella, Mar 25 '19 at 14:08

Shai Rado · Accepted Answer · 2019-03-25T13:39:29.877

If you mean in your post to check if a certain worksheet exists in an Open workbook, then you can test something like in the code below:

Public Sub MainDelete()

Dim currentName As String
Dim ShtName As String

ShtName = "Sheet1" ' <-- change "Sheet1" to your sheet name

' ~~~ call Function and pass the Workbook name and Worksheet name
xRet = IsWorksheetOpen(currentName & ".xlsx", ShtName)

' rest of your code

End Sub

'=================================================================

Function IsWorksheetOpen(WBName As String, WShtName As String) As Boolean

    Dim xWb As Workbook
    Dim xSht As Worksheet

    On Error Resume Next
    Set xWb = Application.Workbooks(Name)
    On Error GoTo 0
    If Not xWb Is Nothing Then
        On Error Resume Next
        ' check also if worksheet is found in Workbook
        Set xSht = xWb.Worksheets(WShtName)
        On Error GoTo 0
        If Not xSht Is Nothing Then ' sheet exists
            IsWorksheetOpen = True
        Else
            IsWorksheetOpen = False
        End If
    Else
        IsWorksheetOpen = False
    End If

End Function

Thank you for the help! My current name is the activesheet.name " currentName = ActiveSheet.Name" or maybe what your referring to is workbook in the currentnane? — marubindesu, Mar 25 '19 at 14:27
@VinIsLearning `currentName` is the name of the Excel file (Workbook), `WShtName` if the sheet name, in your case I guess `WShtName = ActiveSheet.Name` — Shai Rado, Mar 25 '19 at 14:46

score 1 · Answer 2 · answered Mar 25 '19 at 13:36

1

To check eg. if a workbook is opened already by another computer in the network or another Excel instance use something like this:

Function isFileOpen(filename As String) As Boolean
    Dim fileNum As Integer
    Dim errNum As Integer

    On Error Resume Next
        fileNum = FreeFile()

        Open filename For Input Lock Read As #fileNum
        Close fileNum

        errNum = Err
    On Error GoTo 0

    Select Case errNum
        Case 0  'No error
            isFileOpen = False

        Case 70 'Permission denied
            isFileOpen = True

        Case Else
            Error errNum
    End Select
End Function

https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open

answered Mar 25 '19 at 13:36

Pᴇʜ

56,719
10
49
73

Thank you for the help but I tried to code it but It can't detect even thougth the file exist. I have a question about the freefile() what is it? is that a function and where is it located? currentName = ActiveSheet.Name If isFileOpen(currentName & ".xlsx") Then Call Warnings(7) Else MsgBox "Do nothing" End If – marubindesu Mar 25 '19 at 14:15
@VinIsLearning [FreeFile](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function) is a built-in VBA function. – Pᴇʜ Mar 25 '19 at 15:19
Ah, I see! regarding the code, I have an error in this line " Error errNum" the last 3 line. The value of errNum is 53 that's why it went to case else, Im gonna review again the given link that you gave :) – marubindesu Mar 26 '19 at 00:16
@ Pᴇʜ That it mean that I have a different error? and Is it for only drive C? – marubindesu Mar 26 '19 at 00:20
@VinIsLearning Error 53 means file not found. – Pᴇʜ Mar 26 '19 at 06:57
Yes! I noticed that the file not found, so I backtraced the problem and the mistake is only the path of the File :D Thank you for the help. – marubindesu Mar 26 '19 at 08:06

How will I know if my worksheet is also opened to another workbook

2 Answers2