Check if an Excel file is open

[scott]

I searched for this question and found an answer posted by John Nurick on
2/27/07. I put his into my form and it seems to wokr very well, but I get a
message at the end that says the database cannot find the object 0 (if the
file is not open) or -1 (if the file is open). What am I doing wrong?

The function is:

Function IsFileAvailable(FileSpec as String) As Boolean
If Len(Dir(FileSpec)) > 0 Then 'make sure file exists
lngFileNum = FreeFile()
On Error Resume Next
Open FileSpec For Input Lock Read Write As #lngFileNum
If Err.Number = 0 Then 'file opened successfully
IsFileAvailable = True
Close #lngFileNum
Else 'file could not be opened, for whatever reason
IsFileAvailable = False
End If
On Error GoTo 0
End If
End Function

I invoke the function by this code:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim Filespec As String
Filespec = "f:\budgets\time08.xls"
DoCmd.OpenFunction IsFileAvailable(Filespec)

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

 

[Douglas J. Steele]

As far as I know, OpenFunction is only for use with user-defined function in
SQL Server.

How do you want to use the function?

What you need is

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim Filespec As String

Filespec = "f:\budgets\time08.xls"
If IsFileAvailable(Filespec) Then
' Do whatever you want to do if the file's available.
Else
' Do whatever you want to do if the file's not available.
End If

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

 

[scott]

You anticipated what I needed. Thanks.

As far as I know, OpenFunction is only for use with user-defined function in
SQL Server.

How do you want to use the function?

What you need is

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim Filespec As String

Filespec = "f:\budgets\time08.xls"
If IsFileAvailable(Filespec) Then
' Do whatever you want to do if the file's available.
Else
' Do whatever you want to do if the file's not available.
End If

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub