| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| removing a macro from a workbook | Excel Worksheet Functions | |||
| How do I resolve a problem with sharing a workbook | Excel Worksheet Functions | |||
| Problem Sharing Workbook | Excel Discussion (Misc queries) | |||
| Problem removing hyperlinks in workbook | Excel Discussion (Misc queries) | |||
| Problem executing a macro from different workbook where it is | Excel Discussion (Misc queries) | |||
Linear Mode
