Access violation with reprotecting shared workbook

G

Guest

Guest
I'm trying to run the following piece of code on some shared workbooks, but I keep getting an Access violation which dumps me out of Excel (0xc0000005 0x3003e719). I'm running Excel 97 SR-2 under Win NT: -

ActiveWorkbook.UnprotectSharing myPass
ActiveWorkbook.ProtectSharing FileName:=FullName, SharingPassword:=myPass

The code unprotects the workbook and saves it OK, but crashes when trying to reprotect it. Any ideas? Cheers for any help.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Thanks, Dave, but I couldn't find anything there to match my problem. There's nothing wrong with the actual code though?
 
Upvote 0
Looks ok! As a real looooooonnnnng shot try ThisWorkbook as apposed to ActiveWorkbook or even use it's name.
 
Upvote 0
Loooonnng shot didn't work :), but I got around it with the following, which is more or less what I want to do: -

ActiveWorkbook.PurgeChangeHistoryNow days:=1, sharingPassword:=myPass
ActiveWorkbook.Close True
 
Upvote 0
Very interesting to know! My next loooooonnnnnnngeer shot was to suggest Setting a Workbook Object Variable to the Workbook and accessing it's Properties etc this way.
 
Upvote 0
Could you post your thoughts on that, please, Dave, I've included most of my code below. While purging the history works OK, the filesize of the workbook isn't affected and sometimes even grows (!). I'm getting a bit cheesed off with this, it should be very simple but the protectsharing method just doesn't seem to work properly...

Public Sub OpenSub()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Workbooks.Add
Windows(1).Caption = "Destination"
Do Until Worksheets.Count = UBound(FileNameArray)
Worksheets.Add After:=ActiveWorkbook.Worksheets(Worksheets.Count)
Loop

x = 0
For Each ws In ActiveWorkbook.Worksheets
x = x + 1
ws.Name = FileNameArray(x)
ws.Activate
FullName = DirectoryName & PathNameArray(x) & FileNameArray(x) & EndName
If FileDateTime(FullName) > #3/11/02 12:30:00 PM# Then
Workbooks.Open FileName:=FullName
'Windows(FileNameArray(x) & EndName).Activate

With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
.Worksheets("History").Select
End With
Columns("A:K").Select
Selection.Copy
Windows("Destination").Activate
ActiveSheet.Paste
Windows(FileNameArray(x) & EndName).Activate
Application.CutCopyMode = False
'ActiveWorkbook.UnprotectSharing
'ActiveWorkbook.Save
'ActiveWorkbook.ProtectSharing
ActiveWorkbook.PurgeChangeHistoryNow days:=1, sharingPassword:=myPass
ActiveWorkbook.Close True
Else:
ws.Range("A1").Value = "No change history found."
End If

Windows("Destination").Activate
Next ws

End Sub
 
Upvote 0
Actually, I've just figured out the problem- you can't protect for sharing if the workbook itself is already protected, which is a bit of a bugger as the workbook needs to be protected for its structure and then for sharing. Wierd... :(
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top