xluser2007
04-10-2009, 04:31 PM
Hi All,
I am currently running a macro to open workbooks, but by first checking whether the workbook is currently open.
In order to test, I have actually opened the tetsing workbook.
I am then using the following functions to test for it being OPEN:
By Bob's (XLD's) great KB entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=468):
Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0
Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function
From John walkenbach's great site (http://spreadsheetpage.com/index.php/site/tip/some_useful_vba_functions/):
Private Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
The former returns a TRUE, which is correct, the latter returns a FALSE which is incorrect. Could anyone please explain why this may be occurring and not the latter function saying TRUE?
It should be noted that the file I'm testing is an Excel workbook open in the same instance as the workbook from which I am testing it.
Any help appreciated.
I am currently running a macro to open workbooks, but by first checking whether the workbook is currently open.
In order to test, I have actually opened the tetsing workbook.
I am then using the following functions to test for it being OPEN:
By Bob's (XLD's) great KB entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=468):
Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0
Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function
From John walkenbach's great site (http://spreadsheetpage.com/index.php/site/tip/some_useful_vba_functions/):
Private Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
The former returns a TRUE, which is correct, the latter returns a FALSE which is incorrect. Could anyone please explain why this may be occurring and not the latter function saying TRUE?
It should be noted that the file I'm testing is an Excel workbook open in the same instance as the workbook from which I am testing it.
Any help appreciated.