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,
Add a comment |
2 Answers
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
2 Comments
brettdj
+1 I have deployed this technique several times for checking if a file on a shared network is being used already (although my only concern was that the file was already open in Excel)
Gaijinhunter
If this or another answer works you can 'accept' it as the answer by selecting the arrow on the upper left of it. Thanks!
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 