XL2007:Setting ExclusiveAccess stops coded dead

  • With XL2007 and Vista, I am opening workbooks and modifying them. Some of these are shared, and all have macros. I am trying to set ExclusingAccess to remove sharing, but as soon as I do that, the code stops dead. Sharing is removed, but nothing after that line is processed.

    Below is the macro I'm using. I set the Debug.Print statements to see where it's breaking - I get "Check_6", which is the line before the ExclusiveAccess statement, but I never get "Check_7", even though sharing _is_ removed from the file. As you can see, I've tried just about every setting I can to avoid this, but to no avail.

    If anyone has the magic bullet, I will be so glad!!
    Ed

  • Re: XL2007:Setting ExclusiveAccess stops coded dead


    I did some more investigating. I know the Excel help has a procedure using the ActiveWorkbook to enable ExclusiveAccess, and I've seen it recommended in more than one on-line posting. So I set up a separate macro with the Help code, bracketed by DisplayAlerts, and it worked great!! Stuck a MsgBox at the end just to make sure code processing was continued and it was.

    So then I called that procedure from within my other code - and it broke it dead! I thought maybe it needed a different object (I dunno - I'm throwing everything AND the kitchen sink at this!), so I set that up too.

    As before, the file opens, sharing does get turned off, but code processing stops dead at the ExclusiveAccess line.

    Here's my revised test code:

  • Re: XL2007:Setting ExclusiveAccess stops coded dead


    Well, to add more fuel for thought, I also tried these methods:

    -- I pulled out the code to set ExclusiveAccess and wrote in it into a VBScript, executed the script, and looped the macro until MultiUserEditing was false

    -- I used SaveAs with Access as xlExclusive

    -- I set the workbook object to Nothing, used Activeworkbook exactly like the Help article says, and reset the workbook object
    In every case, the method to turn off sharing worked. But the code immediately stopped dead.

    About the only thing I haven't tried (because it's not available to me) is to put all this in a Visual Basic exe and run it totally independant of the Excel VBE. If no one else has any answers, that may be where I need to go with this.

    Ed

  • [Resolved] XL2007:Setting ExclusiveAccess stops coded dead


    I resolved the issue by avoiding it.

    I re-coded everything to run through Word VBA. That allowed me to take a lot code out of the "master template" Excel file, avoiding potential log jams in the Excel VBE.

    I also am requiring the user to open the Excel files one at a time and manually turn off sharing. That avoids the whole ExclusiveAccess issue altogether.

    It all ran great - did five files in less than five minutes. I can live with that!

    Ed

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!