Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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.
0
donhannam
Asked:
donhannam
1 Solution
 
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
 
GrahamMandenoCommented:
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
 
donhannamAuthor Commented:
Thanks Graham works perfectly

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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now