Problem removing sharing from workbook via Macro

[BenS]

I have hundreds of Excel files that are currently set as "shared" but I want
to remove this feature.

I tried to create code to do this by recording the action of me unsharing
one workbook.

This produces the code:

ThisWorkbook.ExclusiveAccess

I then added Application.DisplayAlerts = False before the line above and
Application.DisplayAlerts = True after the line above to produce the
following. I did this because there is a message box that pops up when you
unshare in Excel warning about various things. I wanted to skip this.


Application.DisplayAlerts = False
ThisWorkbook.ExclusiveAccess
Application.DisplayAlerts = True

When I run this I get an error though. The error is:

Run-Time error '1004':
Method 'ExclusiveAccess' of object '_Workbook' failed

End/Debug/Help etc....

Can anyone suggest why I am getting this error message or how I can alter my
code to unshare a workbook without getting an error?

Many thanks!

 

[Gord Dibben]

This works for me.

Sub foo()
Application.DisplayAlerts = False

If ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.ExclusiveAccess
End If

Application.DisplayAlerts = True
End Sub


Gord Dibben MS Excel MVP

 

[BenS]

Gord,

Thank you so much. Your code worked perfectly and ended my frustration over
this problem. Thanks for all the time this is going to save me!

-Ben

 

[Gord Dibben]

Glad to help.

Now all you have to do is get it to loop through the "hundreds" of workbooks.


Gord