Code Not Working

[PJ Usher]

I am transfering data from a Word 2000 document to an Excel 2000
spreadsheet. I have code, listed below, to check if the Excel spreadsheet
is open. (I modified code written by Ron de Briun)

The way this is written gives me "the file is open" whether the file is open
or not. I'm not sure how to fix this. Any help or pointers will be greatly
appreciated. Thank you.

PJ

Public Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = Not (excel.Application.workbooks(FileName) Is Nothing)
End Function

Sub Test( )
If IsOpen("test.xls") Then
MsgBox "the file is open"
Else
MsgBox "the file is not opened"
End If
End Sub

 

[Helmut Weber]

Hi,
sometimes a shorter way to a working solution is to start
from scratch. I don't know where the bug is in the code you posted.
This works for me here and now:
---
Public Function IsWorkbookOpen(ExcName As String) As Boolean
IsWorkbookOpen = False
ExcName = LCase(ExcName)
Dim i As Integer
Dim oExl As Excel.Application
On Error GoTo NoExcel
Set oExl = GetObject(, "Excel.Application")
For i = 1 To oExl.Workbooks.Count
If LCase(oExl.Workbooks(i).Name) = ExcName Then
IsWorkbookOpen = True
Exit Function
End If
Next
Exit Function
NoExcel:
MsgBox "Excel not running at all"
End Function
Sub Test501()
MsgBox IsWorkbookOpen("test.xls")
End Sub
Note: Lowercase seems to be essential.
I got "false" when the name was "test.XLS".
And the possible question for Path & Filename
remains open.
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Perry]

Another way of testing whether the workbook is open and not utilizing
Excel as automation server:

Function IsOpen(ByVal FileName As String) As Boolean
Dim i As Integer
i = FreeFile
On Error GoTo ErrHandler
Open FileName For Binary Access Read Lock Read As #i
Close #i
ExitHere:
Exit Function
ErrHandler:
IsOpen = True
Resume ExitHere
End Function

Krgrds,
Perry

 

[PJ Usher]

Hi

I would recieve an active x error unless I had excel open. My apologies for
not clarifying my criteria. I was trying to check if someone else had the
excel spreadsheet open.

PJ

 

[PJ Usher]

Hi Perry

I always get a true response.

PJ

Another way of testing whether the workbook is open and not utilizing
Excel as automation server:

Function IsOpen(ByVal FileName As String) As Boolean
Dim i As Integer
i = FreeFile
On Error GoTo ErrHandler
Open FileName For Binary Access Read Lock Read As #i
Close #i
ExitHere:
Exit Function
ErrHandler:
IsOpen = True
Resume ExitHere
End Function

Krgrds,
Perry

 

[Perry]

PJ,

Uhh?
Ok, the code itself shud work.
I've used it reliably through dozens of projects...
There must be something else in yr code situation
causing the distortion

Try following:
I'm now using yr code example and adjusted it a bit:

Public Function IsOpen(FileName As String) As Boolean

On Local Error GoTo ErrHand

IsOpen = excel.Application. _
workbooks(FileName).Name = FileName

ExitHere:
Exit Function
ErrHand:
Resume ExitHere
End Function

Krgrds,
Perry