How to check if a file is open

[Fitz1000]

I am trying to write and If statement to see if a file is open (if it is, I need to end the macro, if it is not, I need to proceed to the next instruction). I have found IfFileExists but this is not what I need. I found this article http://support.microsoft.com/default.as ... US;q138621 in the MS Knowledgebase but can't seem to make it work in Macro Scheduler. Anyone have an easy way to do this?

[Marcus Tettmar]

Here you go:

Code: Select all

VBSTART
' 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 occurs because there is
' some other problem accessing the file.
Function IsFileOpen(ByVal sFileName)
  Const ForAppending = 8
  Set oFSO = CreateObject("Scripting.FileSystemObject")

  On Error Resume Next ' Turn error checking off.
  ' Attempt to open the file and lock it.
  Set f = oFSO.OpenTextFile(sFileName, ForAppending, False)
  iErrNum = Err.Number ' Save the error number that occurred.
  f.Close ' Close the file.
  On Error GoTo 0 ' Turn error checking back on.

  ' Check to see which error occurred.
  Select Case iErrNum
    ' 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
      Err.Raise iErrNum
  End Select
End Function
VBEND

VBEval>IsFileOpen("c:\somefile.txt"),IsItOpen
If>IsItOpen=False
  ..
  .. rest of macro here
  ..
Endif

[Fitz1000]

I was soooo close! Thanks for responding, it works like a champ.

[jpuziano]

A very useful bit of code, added to snippets, thanks Marcus!

[Me_again]

It would be nice if it could incorporate a check that the file exists and return an error code. (I know you can do separately in the macro).

The caveat is that not all programs "open" the file they are using.