How to identify an open excel file?

[cunger28]

I have an array set up to cycle through a DoCmd.RunSql command that dumps to
an excel file on my network. Ideally, I want it to over write the existing
file. If somebody is currently in that file however, my entire process
freezes.

Is there any way to identify if a file is open, and if it is, skip the
output for that file and move on to the next one.

Example: I have a file that outputs detail to four files.
Texas.xls
Oklahoma.xls
Arkansas.xls
Louisiana.xls

My array would output the data in the above order. If someone happens to be
in the Oklahoma.xls file, I want to skip the output for that file and move on
to Arkansas.xls.

I have the output array and everything else done. Just need to put together
an if statement that could hopefully identify any open files.

Thanks

 

[Clifford Bass via AccessMonster.com]

Hi,

Does it freeze? What happens if the user then closes out of the Excel
file? Or does it error out? If it errors out, just set up your error
processing to deal with that situation.

Public Sub SomeSub()

Dim lngError As Long

On Error GoTo Handle_Error

' various code

On Error Resume Next
' the code to write to the file
lngError = Err.Number
On Error GoTo Handle_Error
If lngError <> 0 And lngError <> 1234 Then ' Use actual error number that you
get when the file is open
' Some other error - reraise the error
Err.Raise lngError
End If

' other code

Exit_Sub:
Exit Sub

Handle_Error:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_Sub

End Sub

Hope that helps,

Clifford Bass