Problem removing sharing from workbook via Macro
 

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!

Problem removing sharing from workbook via Macro
 

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

On Thu, 25 Oct 2007 11:32:29 -0700, BenS wrote:

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!

Problem removing sharing from workbook via Macro
 

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" wrote:

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

On Thu, 25 Oct 2007 11:32:29 -0700, BenS wrote:

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!

Problem removing sharing from workbook via Macro
 

Glad to help.

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


Gord

On Thu, 25 Oct 2007 15:17:00 -0700, BenS wrote:

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" wrote:

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

On Thu, 25 Oct 2007 11:32:29 -0700, BenS wrote:

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!