If Workbooks.Open Q 2

[willyboy58]

I looked thru past posts and did not find anything to match my questions below.

I have a procedure that uses info from different workbooks. I want to check if a particular workbook is opened or not. If not, then open it. If it is already open, do not open it again or tell me that it is already opened. I have the following code:

Sub IsProp1InvoiceOpen()
If Workbooks.Open("C:\MSOffice\Excel\WorkStuff\Prop1Invoice.xls&quot = False Then
Workbooks.Open Filename:="C:\MSOffice\Excel\Work Stuff\Prop1Invoice.xls"

Else ‘workbook is already open

Exit Sub ‘exit this sub and go to the next one
End If
End Sub

When I run the above procedure, I receive the error message “Object doesn’t support this property or method.” It doesn’t matter if the desired workbook is opened or not, I still get the message. Why?

Also, how can I avoid the message “This workbook contains automatic links…..” and the message “Prop1Invoice.xls is already open. …..” when the workbook is already opened?

TIA

Bill

 

[Zathras]

Workbooks.Open does not return a True/False value that you can test that way. AFAIK, it doesn't return any value.

It sounds like you have multiple books you need to open. Consider this code for a more compact way of doing it:
[blue]

Option Explicit

Sub OpenAllFiles()
  OpenFile "C:\MSOffice\Excel\Work Stuff\Prop1Invoice.xls"
  OpenFile "a.xls"
  OpenFile "b.xls"
End Sub

Sub OpenFile(AFilePath As String)
Dim wkb As Workbook
  On Error Resume Next
  Set wkb = Workbooks(AFilePath)
  If wkb Is Nothing Then
    Workbooks.Open AFilePath
  End If
  Set wkb = Nothing
End Sub

[/color]

 

[willyboy58]

Zathras,

Sorry for taking so long to get you a star. Was hung up with a few other issues the last few days.

Thanks for the help!!!

 

[IvanMoala]

Whilst this is useful for detecting open workbooks for your session of excel, if your file is on a server you will not get this to work, except for the prompt when opening that "the file is in use by xyz"
To get past this you could try 1 of 2 methods
1 API and a VBA routine.

Option Explicit

Sub TestVBA()
    If IsFileOpen("C:\Data.xls") Then
        MsgBox "File is open"
    Else
        MsgBox "File is not open"
    End If
End Sub

Function IsFileOpen(strFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// [URL unfurl="true"]http://www.xcelfiles.com[/URL]

Dim hdlFile As Long

    '// Error is generated if you try
    '// opening a File for ReadWrite lock >> MUST BE OPEN!
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFileName For Random Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close #1
End Function



Option Explicit

'// Note we use an Alias here as using the Actual
'// function name will not be accepted! as an underscore
Private Declare Function lOpen _
    Lib "kernel32" _
    Alias "_lopen" ( _
        ByVal lpPathName As String, _
        ByVal iReadWrite As Long) _
As Long

Private Declare Function lClose _
    Lib "kernel32" _
    Alias "_lclose" ( _
    ByVal hFile As Long) _
As Long

'// Don't use these...here for Info only
Private Const OF_SHARE_COMPAT = &H0
Private Const OF_SHARE_DENY_NONE = &H40
Private Const OF_SHARE_DENY_READ = &H30
Private Const OF_SHARE_DENY_WRITE = &H20
'// Use the Constant below
'// OF_SHARE_EXCLUSIVE = &H10
'// OPENS the FILE in EXCLUSIVE mode,
'// denying other processes AND the current process both read and write
'// access to the file. If the file has been opened in any other mode for read or
'// write access _lopen fails. This is important as if you open the file in the
'// current process = Excel BUT loose its handle
'// then you CANNOT open it again in the SAME session!
Private Const OF_SHARE_EXCLUSIVE = &H10

'If the function succeeds, the return value is a file handle.
'If the function fails, the return value is HFILE_ERROR = -1

Private Function IsFileAlreadyOpen(strFullPath_FileName As String) As Boolean
'// With thanks Matthew Gates (Puff0rz@hotmail.com)
'// Ivan F Moala
'// [URL unfurl="true"]http://www.xcelfiles.com[/URL]
Dim hdlFile As Long
Dim lastErr As Long

hdlFile = -1

' Open file for read/write and exclusive sharing.
hdlFile = lOpen(strFullPath_FileName, OF_SHARE_EXCLUSIVE)
' If we couldn't open the file, get the last error.
If hdlFile = -1 Then
    lastErr = Err.LastDllError
Else
    ' Make sure we close the file on success.
    lClose (hdlFile)
End If

' Check for sharing violation error.
IsFileAlreadyOpen = (hdlFile = -1) And (lastErr = 32)

End Function

Sub TestAPI()
'// We can use this for ANY FILE not just Excel!
    If IsFileAlreadyOpen("C:\Data.xls") Then
        MsgBox "File is open"
    Else
        MsgBox "File is not open"
    End If
End Sub

http://www.xcelfiles.com

 

[willyboy58]

Ivan,

The procedure is not on the server, but I appreciate your input.

Bill