agarwaldvk
10-09-2008, 06:46 PM
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
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