I have to write macro code for the following sequences. What will be
the code to do this?
find if file abc.xls is open, if it is open then close it
else
endif
Hi ezil,
Insert a new module into your VBA project and paste the following
function into it:
Public Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
Open filename For Input Lock Read As #filenum ' Attempt to open
file and lock it.
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.
Select Case errnum ' Check to see which error occurred.
Case 0 ' No error occurred. File NOT already open by another
user.
IsFileOpen = False
Case 70 ' "Permission Denied." True if file is open, or has
been renamed, moved or deleted
IsFileOpen = True
Case Else ' Another error occurred.
Error errnum
End Select
End Function
Insert another module into your project and paste the following code:
Option Explicit
Public xlPath As String, FName As String, filenum As Long
Double click on ThisWorkbook and in Workbooks_Open paste the
following code where xlPath should equal the path to the workbook you
want to close and FName should equal the name of the file you want to
close:
xlPath = "C:\Numerical Registers\"
FName = "abc.xls"
Application.ScreenUpdating = False
On Error Resume Next
If Dir(xlPath & FName) <> "" Then 'If abc.xls exists then
If IsFileOpen(xlPath & FName) = True Then 'Check to see
if abc.xls is open
Workbooks(xlPath & FName).Close 'Close abc.xls
End If
Else
Dim Msg, Style, Title, Response
Msg = "The file you are trying to close does not
exist" 'If abc.xls does not exist, diplay message
Style = vbOKOnly
Title = "Is File Open?"
Response = MsgBox(Msg, Style, Title)
Cancel = True
End If
Application.ScreenUpdating = True
Now when you open the file in which you pasted the above code, it will
check to see if ABC is open. If it is, it will close it.
Best regards,
Andy
