CODEWITHSUNDEEP
vbafile-ioexcel
LianxiWang
LianxiWang

Reputation: 11

How to determine a file was in using by other process or not?

I wanted to save data to Excel cells, but it would fail if the Excel file was opened by other process, so how to determine the file was in using by other process or not before write data to it?

Thanks,

Upvotes: 1

Views: 701

Answers (2)

Richard
Richard

Reputation: 1501

Followingon from Issun's answer, if you find that Microsoft's FileLocked function doesn't work (for example, I've found that it'll incorrectly claim that WTV files that are currently being recorded are not locked), then you can use this method instead which is a little more brutal:

Function FileLocked(sFilename) As Boolean
    Dim oFile, sNewFile, iCount
    If fso.FileExists(sFilename) = False Then
        FileLocked = False
        Exit Function
    End If
    ' Pick a random, unused, temp name
    iCount = 0
    Do
        sNewFile = sFilename & ".tmp" & iCount
        iCount = iCount + 1
    Loop Until fso.FileExists(sNewFile) = False
    ' Try to rename it to that. If it fails, then it is in use
    On Error Resume Next
    fso.MoveFile sFilename, sNewFile
    If Err.Number = 0 Then
        ' It moved okay, so rename it back
        fso.MoveFile sNewFile, sFilename
        FileLocked = False
    Else
        FileLocked = True
    End If
    On Error Goto 0     
End Function

Upvotes: 0

Gaijinhunter
Gaijinhunter

Reputation: 14685

MS has a good example on how to check if a file can be accessed or not before you attempt to open it using a function they wrote called FileLocked.

Sub YourMacro()
   Dim strFileName As String
   ' Full path and name of file.
   strFileName = "C:\test.doc"
   ' Call function to test file lock.
   If Not FileLocked(strFileName) Then
      ' If the function returns False, open the document.
      Documents.Open strFileName
   End If
End Sub

And here is the function (as written by MS):

Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

Reference from Microsoft: http://support.microsoft.com/kb/209189

Upvotes: 2

Related Questions