Workbooks.Open issue

[Pyrgos]

Hello there,

I have the following code written up in a userform:

Private Sub UserForm_Initialize()

Const MasterData As String = "MASTER Data.xlsx"
Dim AsNames As Variant
Dim Counter As Long

'CHECK IF FILE IS ALREADY OPEN OR NOT, IsFileOpen is Function that returns True or False

If Not IsFileOpen("C:\General\2013 Master Folder\" & MasterData) Then
    Workbooks.Open FileName:="W:\General\2013 Master Folder\" & MasterData, ReadOnly:=True 'Open Workbook
End If

and so on...

For several reasons, I need this file to be shared.

The issue is that, when the file is shared, it prompts me with the question about re-opening the file all the same. If I disallow multi-user changes, then the file opens (or not) as expected.

Is anyone aware of way to solve this?

Many thanks in advance.

 

[RoryA]

I suspect your issue is in the IsFileOpen code but since you haven't posted it...

 

[Andrew Poulsom]

I don't know what IsFileOpen is doing, but you may have more success using the WorkbookIsOpen function from here:

Spreadsheet Page Excel Tips: Some Useful VBA Functions

 

[Pyrgos]

Of course, silly me.

Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long
     
    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0
     
    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:   IsFileOpen = True
    Case Else: Error iErr
    End Select
    
End Function

I have copied and pasted this function from some place and since it never gave me problems before, I have constantly used without caring too much about what's in there.

I will have a look at the alternatives on the link, thank you very much for that!

 

[RoryA]

Yep, that's the issue if you share a workbook (which you really really really really really ought to avoid if at all possible). You'll need to loop through all the workbooks and test the FullName property of each against your file path.

 

[Pyrgos]

Yeah, I suspected so. I guess it's time to read more about ExecuteExcel4macro(). I guess that would be the best solution.