iambasil
iambasil

Reputation: 105

Excel VBA file open by same vs other user

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:

  1. The target workbook is already open on their own system, and ideally:
    • Whether it is open in the same Excel instance, or
    • Whether is is open in a separate instance (I believe this could require different actions for copying and pasting ranges, saving etc which I need to learn more about)
  2. The target workbook is open by another network user (so presume it would only open as read-only)

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

Answers (1)

GSerg
GSerg

Reputation: 78183

  1. 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.

  2. 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

Related Questions