Check if Workbook is open and close if it is

[davnao]

Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.

[JConsulting]

Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.

How about something easy

Function fFileOpen(strFile As String) As Boolean
On Error Resume Next
 
    Dim intFile As Integer
    If Dir(strFile) = "" Then Exit Function
 
    intFile = FreeFile()
    Open strFile For Input Lock Read As intFile
    Close intFile
 
    If err <> 0 Then
        fFileOpen = True
    End If
 
End Function
 

[JConsulting]

Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.

About the close part.

Unless you are the person/program with the excel file open, you cannot close it.

[davnao]

[JConsulting]

When I run code, I get a Run Time error '70'; Permission denied when I actually have a file open on code line which reads:

Open strFile For Input Lock Read As intFile

For some reason, won't "resume" as code tells it to on error. Odd

About the close part, only I have the file open so it is my file.

[JConsulting]

[JConsulting]

When I run code, I get a Run Time error '70'; Permission denied when I actually have a file open on code line which reads:

Open strFile For Input Lock Read As intFile

For some reason, won't "resume" as code tells it to on error. Odd

About the close part, only I have the file open so it is my file.

how are you running the code?

[davnao]

how are you running the code?

Pasted your code in a Module.

Running code from a form, on click, right now just asking to display status

MsgBox fFileOpen(fname) ' where fname includes path & filename

Is that what you were asking?

[JConsulting]

Pasted your code in a Module.

Running code from a form, on click, right now just asking to display status

MsgBox fFileOpen(fname) ' where fname includes path & filename

Is that what you were asking?

you're doing everything right...can you capture the error?

if err.number = 77 then
fFileOpen = true
end if

[davnao]

[quote=JConsulting]you're doing everything right...can you capture the error?

Won't let me...seems to get here

Open strFile For Input Lock Read As intFile

and get stuck and will go no further, won't execute the "on error" code at all; tried turning off warnings and still won't execute next line of code.

[JConsulting]

you're doing everything right...can you capture the error?

if err.number = 77 then
fFileOpen = true
end if

Here is an option capturing the errors

Sub TestExcel(strFile)as boolean
    Dim intFreeFile As Integer
 
    On Error Resume Next
    intFreeFile = FreeFile
    Open strFile For Input Lock Read As #intFreeFile
    Select Case Err.Number
        Case 70
            MsgBox "This file is already in use"
                 TestExcel = true
        Case 0
            Close #intFreeFile
        Case Else
            '   Some other error
    End Select
 
End Sub
 

[JConsulting]

Here is an option capturing the errors

Sub TestExcel(strFile)as boolean
    Dim intFreeFile As Integer
 
    On Error Resume Next
    intFreeFile = FreeFile
    Open strFile For Input Lock Read As #intFreeFile
    Select Case Err.Number
        Case 70
            MsgBox "This file is already in use"
                 TestExcel = true
        Case 0
            Close #intFreeFile
        Case Else
            '   Some other error
    End Select
 
End Sub
 

and another if that fails to work for you

Private Function IsFileAlreadyOpen(Filename As String) As Boolean
'   Returns TRUE if the workbook is open
  Dim x As Workbook
  On Error Resume Next
  Set x = Workbooks("c:\test.xls")
  If Err = 0 Then IsFileAlreadyOpen = True _
      Else IsFileAlreadyOpen = False
End Function
 

[davnao]

and another if that fails to work for you

Private Function IsFileAlreadyOpen(Filename As String) As Boolean
'   Returns TRUE if the workbook is open
  Dim x As Workbook
  On Error Resume Next
  Set x = Workbooks("c:\test.xls")
  If Err = 0 Then IsFileAlreadyOpen = True _
      Else IsFileAlreadyOpen = False
End Function
 

Pasted your code with the following in a new module and it worked:

Private Declare Function lopen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function GetLastError Lib "kernel32" () As Long
Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long

Now how do I close a file if it is found open by this code?

[JConsulting]

Pasted your code with the following in a new module and it worked:

Private Declare Function lopen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function GetLastError Lib "kernel32" () As Long
Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long

Now how do I close a file if it is found open by this code?

Dim xlBook As Excel.Workbook, xlApp As Excel.Application
Set xlBook = GetObject("Your Excel File Name")
Set xlApp = xlBook.Parent
xlBook.Save
xlBook.Saved = True
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
 

[davnao]

Dim xlBook As Excel.Workbook, xlApp As Excel.Application
Set xlBook = GetObject("Your Excel File Name")
Set xlApp = xlBook.Parent
xlBook.Save
xlBook.Saved = True
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
 

Thanks a bunch for all your help!

[JConsulting]

Thanks a bunch for all your help!

Happy to help. Let us know if you need any more help.
J