Visual Basic check if the file is open | duplicate closed

Question

Is there a way to check if this(example test.xlsm) file is open ?

Dim v_datenwb As String
v_datenwb = "test.xlsm"
Dim err As String
err = " ist nicht geöffnet!"
Dim op As Integer
op = 0

Do While op <> 1
If IsFileOpen(v_datenweb) = True Then
Workbooks(v_datenwb).Activate
op = 1
Else
If vbCancel = MsgBox(v_datenwb & err, vbRetryCancel, "Error") Then
    Exit Sub
End If
err = " ist nicht geöffnet! Sind sie sich sicher dass das Dokument offen ist ?"
End If
Loop

Public Function IsFileOpen( _
ByVal FileName As String, _
Optional ByVal ViewKind As String ="{00000000-0000-0000-0000-000000000000}" _
) As Boolean

I tried somthing like thatt but it didnt worked. Everytime when the program checked if the file is open the result was False.

I'd appreciate any proposal for a better solution.

I dont want to have a file path. Do you have a solution when i dont have a path ? @dot.Py — Z3RP, Apr 04 '17 at 11:26
We can't see your IsFileOpen function, the one that actually matters. Please post the code of that as well. — vacip, Apr 04 '17 at 11:27
I think that is the problem i'm not good at coding and i think the function is missing i thought that was the function. I got it from this site : https://msdn.microsoft.com/de-de/library/aa300915%28v=vs.71%29.aspx @vacip — Z3RP, Apr 04 '17 at 11:30

dot.Py · Answer 1 · 2017-04-04T11:40:56.533

You defined v_datenwb = "test.xlsm" but you called v_datenweb at If IsFileOpen(v_datenweb) = True Then.

Have you tried this solution?

You just have to change c:\Book2.xls to your desired workbook.

This code has a function called IsFileOpen and a macro called TestFileOpened that calls the function. Sub TestFileOpened()

    ' Test to see if the file is open.
    If IsFileOpen("c:\Book2.xls") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
        '
        ' Add code here to handle case where file is open by another
        ' user.
        '
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
        ' Open the file in Excel.
        Workbooks.Open "c:\Book2.xls"
        '
        ' Add code here to handle case where file is NOT open by another
        ' user.
        '
    End If

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Yes and i gto the Error (in german) : Argumenttyp ByRef unverträglich — Z3RP, Apr 04 '17 at 11:34
How did you implemented it and called the function? Because I just tested this code here and it worked! — dot.Py, Apr 04 '17 at 11:36

score 0 · Accepted Answer · answered Apr 04 '17 at 11:34

A simple logic can be used as below to check if a file is open...

Function IsFileOpen(fileName As String) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(fileName)
On Error GoTo 0
If Not wb Is Nothing Then IsFileOpen = True
End Function

Sub Test()
Dim fName As String
fName = "test.xlsm"
If IsFileOpen(fName) Then
    MsgBox "File is open.", vbExclamation
Else
    MsgBox "File is not open.", vbExclamation
End If
End Sub

Visual Basic check if the file is open | duplicate closed

2 Answers2