Reputation: 105
I am writing a bit of code for a company who will be using it on their network. I'm not on a network like theirs to be able to test, so I'm checking in to try and make sure the code is right first time!
The workbook will interact with other worksheets on their network in various ways, and essentially I need to be able to check if:
I've come across lots of stuff about checking if a workbook is already open, eg: http://support.microsoft.com/kb/291295
But I haven't found anything that clearly differentiates between the two scenarios I explained above.
Thank you
Update
I am now using:
Function IsFileOpenInInstance(FileName As String)
Dim ShortFileName As String
Dim ValidOpenFile As Boolean
Dim ErrNum As Integer
On Error Resume Next
ShortFileName = Dir(FileName)
If FileName = Workbooks(ShortFileName).FullName Then ValidOpenFile = True
ErrNum = Err
On Error GoTo 0
If ErrNum <> 0 Then
IsFileOpenInInstance = False
Else
IsFileOpenInInstance = ValidOpenFile
End If
End Function
Upvotes: 4
Views: 3950
Reputation: 78183
Because of how Excel macros are resolved, Excel workbooks within a single instance are distinguished by unqualified name (file name without full path).
Try accessing Workbooks("File Name.xls")
to see if the workbook is loaded within current instance, and if it is, check FullName
to see if it is indeed the right one.
It does not make a difference, operation-wise, whether or not two workbooks are opened in different instances. Getting a pointer to another instance, however, may be problematic and I don't recommend this (e.g. if you call GetObject("File Name.xls")
, it can return your instance of Excel, not the other one, even if you don't have the file opened). You should work on assumption that all required workbooks are opened in the same instance.
It does not make a difference whether you or another user has opened a workbook. If you try to open the same workbook in another instance of Excel, it will tell you it is blocked same way as if it was opened by someone else.
Upvotes: 1