CODEWITHSUNDEEP
excelvba
marubindesu
marubindesu

Reputation: 69

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

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

Upvotes: 0

Views: 151

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57743

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

Upvotes: 1

Related Questions