Macro File Open

[dorn]

I have multi user macro that compiles data from each user. When they execute it, it opens another file to track some data. But for each user it opens the same file. If user B starts their macro while user A is still in the "tracking file" they get the - whose changes get saved prompt. What I want is that when the macro tries to open the tracking workbook, if it is already open by someone else, to return message box then exit out. I know how to do the message box and exit out. I don't know how to have the macro know that the file it is trying to open is already in use. Appreciate the help......thanks

 

[hippiehacker]

[COLOR=#0000ff]Function[/COLOR] IsFileOpen(FileName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) 
    [COLOR=blue]Dim[/COLOR] iFilenum [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
    [COLOR=blue]Dim[/COLOR] iErr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
     
    [COLOR=blue]On Error Resume Next[/COLOR] 
    iFilenum = FreeFile() 
    [COLOR=blue]Open[/COLOR] FileName [COLOR=blue]For[/COLOR] [COLOR=blue]Input[/COLOR] Lock Read [COLOR=blue]As[/COLOR] #iFilenum 
    [COLOR=blue]Close[/COLOR] iFilenum 
    iErr = Err 
    [COLOR=blue]On Error Goto[/COLOR] 0 
     
    [COLOR=blue]Select Case[/COLOR] iErr 
    [COLOR=blue]Case[/COLOR] 0:    IsFileOpen = [COLOR=blue]False[/COLOR] 
    [COLOR=blue]Case[/COLOR] 70:   IsFileOpen = [COLOR=blue]True[/COLOR] 
    [COLOR=blue]Case[/COLOR] Else: Error iErr 
    [COLOR=blue]End Select[/COLOR] 
     
[COLOR=blue]End Function[/COLOR]

use it like

[COLOR=blue]Sub[/COLOR] test() 
    [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] IsFileOpen("C:\temp\test.xls") [COLOR=blue]Then[/COLOR] 
        Workbooks.Open "C:\temp\test.xls" 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
[COLOR=blue]End Sub[/COLOR]

 

[dorn]

hippiehacker - You ROCK!!
Thanks for quick reply. Since most of that is over my pea brain, I had to do a little playing - but you got me there. Thanks again for the assist!!