Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Access violation with reprotecting shared workbook

  1. #1
    Guest

    Default

    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.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

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

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



  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •