Excel öffnen, aber nur wenn nicht zum Arbeiten gesperrt

[Detlef]

Hi,

schaff's irgendwie nicht, vielleicht kann mir jemand von Euch helfen.

Pseudocode:
datei = "f:\datei.xls"
' wenn Excel nicht gestartet, Excel starten
' wenn datei nicht geöffnet, dieses öffen
' wenn datei von anderem User gesperrt, dann raus <--- ist
mir am wichtigsten
'..... (ab hier wird's klar)

Vielen Dank im voraus
Detlef

 

[Detlef]

Excuse the original message, thought it was a german newsgroup. But there's
none.
Therefore i try it in english, hope you can unterstand me.

Pseudocode:

file = "f:\file.xls"
' if excel isn't started, start excel
' if file isn't open, open it
' if file is locked by another network user, then exit <---- this is the
most important
'..... now business as usual

Thanx in advanced
Detlef from Germany

 

[Chip Pearson]

Try code like the following:


Sub AAA()

Const FILE_NAME = "H:\Book3.xls" '<<< CHANGE
Dim XL As Object
Dim WB As Object
On Error Resume Next
Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then
Set XL = CreateObject("Excel.Application")
If XL Is Nothing Then
MsgBox "cannot access Excel"
Exit Sub
End If
End If
For Each WB In XL.workbooks
If WB.FullName = FILE_NAME Then
WB.Activate
Exit Sub
End If
Next WB
If WB Is Nothing Then
If IsFileOpen(Filename:=FILE_NAME) = True Then
MsgBox "File is open by another non-excel process"
Exit Sub
Else
XL.workbooks.Open Filename:=FILE_NAME
End If
End If



End Sub


Public Function IsFileOpen(Filename As String) As Boolean
Dim FileNum As Integer
Dim ErrNum As Integer
On Error Resume Next
If Dir(Filename) = "" Then
IsFileOpen = False
End If
FileNum = FreeFile()
Open Filename For Input Lock Read As #FileNum
Close FileNum
ErrNum = Err
On Error GoTo 0
If Err.Number = 0 Then
IsFileOpen = False
Else
IsFileOpen = True
End If
End Function