Testing if File is Already Open

dramey1

New Member
Joined
Jun 21, 2017
Messages
22
I am trying to test whether or a certain file is open already, and if it is closed I am saving data to it. For some reason, my code was working just fine and randomly it stopped working and giving me the message "Sub or Function not defined". I don't understand why it suddenly stopped working when I don't believe I changed anything.
CODE:
'OPEN- SCHEDULE RECHECK
If FileAlreadyOpen(FilePath) = True Then
Application.OnTime Now +TimeValue("00:00:05"), "TransferData"
Worksheets("ASCE").TransferData_Click.Enabled = False
Worksheets("ASCE").TransferData_Click.Caption = "Saving... Please wait"

It says FileAlreadyOpen is not defined, but I don't understand why it worked before and not now.
Any help is greatly appreciated.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Imabus

New Member
Joined
Mar 4, 2013
Messages
32
Where there is a chance that I will hit a workbook that is open I use the code below, it allows it to have multiple attempts so you have fewer chances of not completing the task if the file might be in use by another user running a macro.

If you are getting a new error then I would suspect that something has been altered that you don't know about/didn't notice or maybe a connection to a shared drive where an add-in holding the function is stored has an issue?

Check where the function is stored and that nothing has edited it first, also check that you haven't another module with the same name as the function, long shot but it would mess things up.

Code:
restart:
If IsWorkBookOpen("N:\VBA Add-in\CRM_Log.xlsx") = False Then

....
....
....

Else
    Application.StatusBar = "Another user is accessing the file...attempting to reconnect " & errcount + 1
    Application.Wait (Now + TimeValue("0:00:03"))
    errcount = errcount + 1
    If errcount > 10 Then
        Application.StatusBar = ""
        Exit Sub
    End If
    GoTo restart
End If

and then in a separate module

Code:
Function IsWorkBookOpen(filename As String) As Boolean
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top