Access violation with reprotecting shared workbook

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.

[Dave Hawley]

Hi

You best bet with this sort of problem may well be the MSKB:

http://support.microsoft.com/default...-US&pr=kbinfo&

[Mudface]

Thanks, Dave, but I couldn't find anything there to match my problem. There's nothing wrong with the actual code though?

[Dave Hawley]

Looks ok! As a real looooooonnnnng shot try ThisWorkbook as apposed to ActiveWorkbook or even use it's name.

[Mudface]

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

[Dave Hawley]

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.

[Mudface]

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

[Mudface]

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...