How to check if a file is open using Excel VBA?

[agarwaldvk]

Hi Everybody

I have some VBA code (not mine - I got it of the net) to determine if a file is open.

My requirement is to be able to :-
1. Determine is a particular file (can be any file generated by any application viz Excel, Access, SAS etc etc. (or even a log file for that matter)
2. If it is open, then determine the applicaion that is using it
3. Close that file - don't mind if that necessitates closing the application using that file.

Any suggestions?

Here is the code that I have :-


'This bit is mine
Sub test()
Dim fileOpen As Boolean
Dim fullFileName As String
Dim fso As Object
Set fso = CreateObject("Scripting.fileSystemObject")
fullFileName = "J:\OperationsInnovation\Reporting and Forecasting\Forecasting\Historical Data Files\HistoricalData.sas"
fileOpen = FileAlreadyOpen(fullFileName)

If Not (fileOpen) Then
SetAttr fullFileName, vbNormal
fso.DeleteFile fullFileName
End If
End Sub



'This bit is not mine
Function FileAlreadyOpen(fullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:\FolderName\FileName.xls") Then...
Dim f As Integer
f = FreeFile
On Error Resume Next
Open fullFileName For Binary Access Read Write Lock Read Write As #f
Close #f
' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
FileAlreadyOpen = True
Err.Clear
'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
Else
FileAlreadyOpen = False
End If
On Error GoTo 0
End Function



Using the above code, for some reason it did not recognize the SAS program code file (extension .sas file) as open when I made sure it was open in SAS application, and hence it got deleted and SAS application did not seem to mind - isn't that a bit odd?

I am not able to get the name of the application using the file for starters. I hence did not even try to get to the point to closing that file and/or the application using that file.


Best regards


Deepak

[Kenneth Hobs]

XLD has done something similar as posted in http://www.mrexcel.com/forum/showthread.php?t=247575

However, if you open the file in NotePad, these routines will not show it open. Applications like Excel will lock a file when it is opened.

When using a routine like these, a boolean does not give the full picture. You should first check that the file actually exists. You can DIR for that.

If the goal is to delete the file if not open, I would use an On Error with KILL.

There is an API routine that can tell you which application Windows associates to some file extension. This does not mean that that application is the one that has the file open. Of course in most cases, it would be a good assumption. However, applications like Excel could have 2 instances open. If you want me to post the API routine or a link to it, let me know.

[GTO]

Sir:

To respectfully interject---I would be most interested.

Thank you so much,

Mark

[Kenneth Hobs]

No sense reinventing the wheel, Killian posted the API routine to find the EXE in http://www.vbaexpress.com/forum/showthread.php?t=6653

There are parts missing in the link above. This routine will do it too.
'http://www.pcreview.co.uk/forums/thread-2660940.php
Private Declare Function FindExecutable& Lib "shell32.dll" Alias _
"FindExecutableA" (ByVal lpFile$, ByVal lpDirectory$, ByVal lpResult$)

Private Function GetFileAssociation$(ByVal sFile$)
GetFileAssociation = "File not found !"
If Dir(sFile) = "" Or sFile = "" Then Exit Function
GetFileAssociation = "No association found !"
Dim i&, E$: E = String(260, Chr$(0))
i = FindExecutable(sFile, vbNullString, E)
If i > 32 Then GetFileAssociation = Left$(E, InStr(E, Chr$(0)) - 1)
End Function

Private Sub test()
Dim s As String
s = ThisWorkbook.FullName
MsgBox GetFileAssociation(s), , s
End Sub
If you have the vb.net v2 framework files installed, you could run a routine that I wrote that checks IsEXERunning. I posted it in a WordPerfect forum. WordPerfect's programming language, PerfectScript, has a command call Applocate that allows wildcards. It is an easy method to check if a window's caption title contains part of a filename which most do. Anyway, here is the link to the vb.net EXE that I wrote. It does not require WordPerfect. http://www.wpuniverse.com/vb/showthread.php?threadid=20345

[GTO]

Thank you very much :-)

Will read-up this afternoon, way too late for this one...

Thank you again,

Mark