VBA if file is open

[iktpq]

Hey hey,

ive hit a wall and looking for some help.

what i'm looking for is to check if a file is open on a sharepoint site. if it is to flag a msgbox and end the sub else run my code

i've looked around and found some code that i assumed would work.

 ' 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 will occur 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

Sub TestFileOpened()
       ' Test to see if the file is open.
       If IsFileOpen("[URL]http://sharepointsite/book1.xlsx[/URL]") Then
           ' Display a message stating the file in use.
           MsgBox "File already in use!"
   
       Else
           
'My code
        
       End If
   End Sub

This issue i'm having is i get a debug on Error errnum. i've tried looking around but cant find a reason for this to be throwing back the error.

i'm using excel 2010 if that has anything to do with it. i understand that this function is not built into excel which is why this bit of code is needed


Thanks for your help

 

[Jaafar Tribak]

That means that another error is happening when trying to open the file other than the error that occurs when the file is already open
Which error number are you getting ?

Have you tried removing the last select lines ? :

Case Else
Error errnum

 

[iktpq]

That means that another error is happening when trying to open the file other than the error that occurs when the file is already open
Which error number are you getting ?

Have you tried removing the last select lines ? :

Case Else
Error errnum

Thanks for the help.

i've removed this bit of code. i no longer get the error but it doesnt flag that its open. (to test this i've got someone else to open the file)

 

[Jaafar Tribak]

I don't know ... I have just tested the function with an open workbook in a second instance of excel and gave me error 70 as expected

Look up the error that you are getting and see what it means

 

[iktpq]

I don't know ... I have just tested the function with an open workbook in a second instance of excel and gave me error 70 as expected

Look up the error that you are getting and see what it means

Thanks i'll look into that. could it be that its opening the book from a sharepoint site ?

 

[Jaafar Tribak]

Thanks i'll look into that. could it be that its opening the book from a sharepoint site ?

Maybe, I am not sure ... I suggest you see what error you get and do a net search for that error