In Access vba detect whether an excel file is open

I want to read/update information off an excel spreadsheet but don't want to do this while someone has it open. You cannot delete a file when someone has it open so system must record somewhere? - is there a way of finding out if a file is opened by someone - this file will be on a network drive. I looked on the FileSystemObject but could not see anything obvious to tell me.
donhannamAsked:
Who is Participating?
 
GrahamMandenoConnect With a Mentor Commented:
Hi Don

Try this function:
Public Function FileIsLocked(strFileName As String) As Boolean
Dim hFile As Long
  On Error Resume Next
  hFile = FreeFile
  Open strFileName For Input Lock Read Write As #hFile
  Select Case Err.Number
    Case 0
      Close #hFile
      FileIsLocked = False
    Case 70
      FileIsLocked = True
    Case Else
      MsgBox "Error opening " & strFileName & vbCrLf & Err.Description, vbExclamation
      FileIsLocked = True
  End Select
  Err.Clear
End Function

Open in new window


It simply attempts to open the file for exclusive read access and checks the failure status.

Best regards,
Graham Mandeno [Access MVP 1996-2011]
0
 
danishaniCommented:
Check below thread out of Ron de Bruin, scroll down, and you find a nice solution:
http://www.rondebruin.nl/exist.htm

Or this thread:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

Hope this helps,
Daniel
0
 
donhannamAuthor Commented:
Thanks Graham works perfectly

danishani: Think the links you had effectively do the same thing but prefered the one Graham provided.
0
All Courses

From novice to tech pro — start learning today.