Checking If Excel File Is In Use

[DMT Dave]

Hi Guy's, Here I have a function and a Sub to call the function, i am getting a message saying Sub Or Function Not Defined

I am checking if another user has got the excel file open or not, then open it if another user has not got it open

OR EVEN BETTER, HOW DO I SHARE THE FILE OVER A NETWORK ?, This maybe the better option ???

Function

Function IsXLOpen(FileName As String)Dim iFilenum As Long
Dim iErr As Long


On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0


Select Case iErr
Case 0: IsXLOpen = False
Case 70: IsXLOpen = True
Case Else: Error iErr
End Select


End Function

Sub

srcPath = "T:\DMT Ltd\XL Files\"
srcFile = "New Items.xlsx"
If Not IsXLOpen(srcPath & srcFile) Then    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    xl.Workbooks.Open srcPath & srcFile, True, False
    End If

[Minty]

What is FreeFile() ?

I don't recognise that as a valid VBA function.

[DMT Dave]

Hi Minty, took it from here https://www.mrexcel.com/board/thread...ate-it.978907/

Thinking about, please correct me if i am wrong, after thought, it maybe an excel function ?

[Minty]

My mistake, I've just checked a similar code I use and it uses the exactly the same function (show's what I know...!)

Where have you stored the function - It must be in a separate module and the module cannot be called the same as any of the functions?

[DMT Dave]

I have a module called PublicFunctions

The declare functions in there, I have renamed from IsFileOpen to IsXLOpen to make it a unique function

If i am taking on board correctly, you maybe saying to me create a new function and call is something like CheckFileFunctions ? or similar

[Minty]

Well I use the same code and it is called IsFileOpen()
It's not specific to XL per se.

Have you got option explicit at the top of all you code modules ? You should have if you haven't.
Which line is it highlighting when you try and compile it.
Have you saved the module since you renamed it?

[DMT Dave]

Option Compare Database at the top

Just changed the function name to IsFileOpen because i already have that function

Just comes up compile error sub or function not defined, is this because i need to change Option Compare Database to Option Explicit ?

[DMT Dave]

Compile Error and highlights IsFileOpen also changed Option Compare Database To Option Explicit

[Minty]

Don't change it you need both;

Option Compare Database
Option Explicit


In the VBA editor Right click on IsFileOpen and select Definition.
What is the module you have saved it in called? You should only have it once anywhere.

[Welshgasman]

Compile Error and highlights IsFileOpen also changed Option Compare Database To Option Explicit

How can it be highlighting IsFileOpen when you named it IsXLOpen and are using that?
Your error is elsewhere?
Find the IsFileOpen in your functions.

[DMT Dave]

I am now using IsFileOpen as I had forgot I had that as a Function already

This is the function I already had, now using this and not the one in earlier post

Public 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.




    Select Case errnum




        Case 0
         IsFileOpen = False


        Case 70
            IsFileOpen = True


        Case Else
            Error errnum
    End Select


End Function

Sub Compile Error

[Welshgasman]

You keep having Dim on the same line?, is that the site or you?
I would have had the function as

Public Function IsFileOpen(FileName As String) As Boolean

Have you put the function in the correct type module?
Do other functions in that module work as expected.?
All can be tested from the Immediate window.

[Micron]

OR EVEN BETTER, HOW DO I SHARE THE FILE OVER A NETWORK

Share, as in have multiple concurrent users? Unless Excel has changed drastically, you can't have concurrent users in a workbook no matter where it is stored. I too wonder why the function doesn't return a Boolean. Also wonder why this approach because IMO there are lots of examples out there that I'd consider to be better. I'd use the posted approach to write to something simple like a txt file, certainly not for writing data to a worksheet. For that, I'd use automation if I couldn't use other methods like TransferSpreadsheet. However, none of that will help with the error at hand, and for that I can't see the cause here.

[Minty]

I'd never noticed that the return type wasn't declared, and I've been using that code for absolutely years. (It's in an old module of commonly used things I have in virtually every database.)
It must work as is obviously, but I have just added the declaration for good measure.

[Micron]

I'd never noticed that the return type wasn't declared, and I've been using that code for absolutely years. (It's in an old module of commonly used things I have in virtually every database.)
It must work as is obviously, but I have just added the declaration for good measure.

IIRC, a function has a default return type if not specified. However, I cannot recall if the default is a string or a variant. If it is a variant, that might explain why a function with no return type could work. I tend to not rely on leaving things to chance, so if I expect a function to return a value to a procedure, I would not write it without declaring a return type.