How to make macro wait on file?

[KBS_DK]

Hello

This is my first post, so be nice

My problem is, that I need a macro in Excel to wait untill my work file is writable again. Because there is a another user, using the file.

Macro:
When the user has closed the file down, my macro continue.
Or give notice of that file is busy

Is there a way that I can get Excel macro to it?

If you can help me, I will be happy

Regards
Klaus

 

[KBS_DK]

Still needs help

 

[xenou]

What kind of file is the work file? What are you doing with it?

 

[KBS_DK]

It's a excel fil, where other excel fil but some data in...
To avoid errors, it needs tjek the fil.

Jeg thing got the answer here:

sub open_fil

Dim KBS2 As String
KBS2 = "C:\Electronics\Oversigt.xls"
If IsFileOpen(KBS2) Then
Do
Application.Wait Now + TimeSerial(0, 0, 2)
MsgBox "Oversigt arket er i brug - prøver igen"
Loop Until IsFileOpen(flnm) = False
End If
end sub



Function IsFileOpen(filename As String)

Dim filenum As Integer, errnum As Integer
On Error Resume Next '~~> Turn error checking off.
filenum = FreeFile() '~~> Get a free file number.
'~~> Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum '~~> Close the file.
errnum = Err '~~> Save the error number that occurred.
On Error GoTo 0 '~~> Turn error checking back on.
'~~> Check to see which error occurred.
Select Case errnum
'~~> No error occurred.
'~~> File is NOT already open by another user.
Case 0
IsFileOpen = False
'~~> Error number for "Permission Denied."
'~~> File is already opened by another user.
Case 70
IsFileOpen = True
'~~> Another error occurred.
Case Else
Error errnum
End Select

End Function

 

[xenou]

Not sure. Sometimes you can open Excel files that others are using - it will be read-only but still available. So it depends on what you are doing. If the file is in use you could be waiting for seconds, minutes, or hours until its free - I'd just ask the person using the file rather than loop in a macro for who knows how long.

Normally, you don't use this low-level file manipulation type of code to open Excel - you can use the Excel object model with its own open/close methods to work with Excel files.

ξ

 

[KBS_DK]

Hello

My problem is that this excel sheet is a overview of jobs that need to be performed.

Macro is running out and type in the sheet when the job is done, therefore, the sheet must always be available.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
Who can I use the Excel object model???<o></o>