if file is open save and close that file

sobi_256 · Jan 31st 2022

Hi guys
i am opening a text file via vba. I am struggling to check if the text file is already open or not, if its open can we save and close that file. if its not open then run some code. i tested the below code , even if file is open it says in the msg box that file is closed, i get the same message if file is closed(which is correct) .any help please

regards

Code

Dim ret

ret = IsWorkBookOpen("C:\Users\user\Desktop\delivery.txt")

    If ret = True Then
        MsgBox "File is open"
    Else
'code to save and close the file
        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

Display More

**royUK** · Jan 31st 2022

Try this Function to test if an error caused by opening the text file occurs.

Code

Private Function IsFileOK(FullPath As String) As Boolean
Dim f As Integer
IsFileOK = True
On Error GoTo err
f = FreeFile
Open FullPath For Append As #f
Close #f
Exit Function
err|:
IsFileOK = False
End Function

Display More

sobi_256 · Jan 31st 2022

hi it is showing file is open even if file is closed

if file is open save and close that file

Participate now!

Share