VBA, Check If Text File is Open

When you are working with text files, it is a good idea to check for errors when opening the file. Without the proper error handling the program might terminate unexpectedly. The file and code related to this article can be downloaded here.

Below are a two reasons why the program might terminate execution upon opening a file:

  1. The file is already in use by another program.
  2. The Index number used to open the file is currently in use. For more information about the Index number used in opening text files please see Excel VBA Reading Text Files.

The code below asks the user to select a text file to open. If the file is already open by another program a message box is displayed. 

Sub Example5()
Dim intResult As Integer
Dim strPath As String

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Show
If intResult <> 0 Then
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    On Error GoTo lblError:
    Open strPath For Input As #1
    MsgBox ("Opening the file was successful")
End If
Close #1
Exit Sub

lblError:
MsgBox ("There was an error opening the file. Implement the necessary actions")
Err.Clear
Close #1
End Sub

The lines below creates an open file dialog and asks the user to select a text file. For more information about open file dialogs please see Excel VBA, Open File Dialog:

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Show

The line below returns the selected text file’s path:

strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

If the file is being used the error handler below catches the error and tells the program to continue execution at the highlighted label:

On Error GoTo lblError:
Open strPath For Input As #1

The code will continue at this line if an error is thrown:

lblError:

If the file is opened successfully the message box below is displayed:

VBA, Check File Status
If something goes wrong while opening the file the message box below is displayed:

VBA, Check File Status Not Open

The file and code related to this article can be downloaded here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *