Problem removing sharing from workbook via Macro

B

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!
 
G

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
 
B

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
 
G

Gord Dibben

Glad to help.

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


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top