How to use VBA to determine if shared Excel workbook being used?

[krisa]

I have an application in Access 2K where I write some data to, and run a macro in, an Excel workbook. The workbook can be shared by various users on a network. For the procedure to run, the workbook must be closed. How do I determine if anyone has the workbook open, and if so, programmatically close it? There doesn't appear to be a handy "Workbook.AlreadyOpen" property.

Using VBA, of course.

Perhaps I should also ask: is it necessary to close all open instances of the workbook, or only the one the person running this procedure may have open? I have no way to test this beforehand, since I don't have a network available to test on.

Thank you for any help!

Regards,
Krisa

 

[uberpudge]

This worked for me when I tested it:

Option Explicit

Sub Test()
Dim varFileName As Variant
Dim wkbkShared As Workbook
Dim strMyName As String
Dim intDex As Integer
Dim intMyUserStatus As Integer

varFileName = Application.GetOpenFilename("Microsoft Excel Workbooks (*.xls), *.xls&quot

'If a string was returned, then we have a valid file name.
If Information.VarType(varFileName) = VbVarType.vbString Then
'Return the appropriate workbook into an object variable.
Set wkbkShared = Workbooks.Open(varFileName)

'Check to see if it's shared.
If wkbkShared.MultiUserEditing = True Then
'Get my user name. Not necessary for operation. You may use this if you like.
strMyName = VBA.Environ("USERNAME&quot
'Loop through to see which user I am. Not necessary for operation. You may use this if you like.
For intDex = LBound(wkbkShared.UserStatus) To UBound(wkbkShared.UserStatus)
If wkbkShared.UserStatus(intDex, 1) = strMyName Then
intMyUserStatus = intDex
End If
Next
'Set the workbook for MY exclusive access.
wkbkShared.ExclusiveAccess
'Double check the Err object to see if the action was cancelled.
If Err.Number = 1004 Then
Interaction.MsgBox PROMPT:="Action was cancelled by the user."
'Reset error number.
Err.Number = 0
End If
End If
Stop
Else
'Must be the Boolean "False". Take appropriate action.
Interaction.MsgBox PROMPT:="Action was cancelled by the user."
End If
End Sub

Hope this helps,
Pete