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!!
Sub OpenAndUnshare() Dim wkb As Workbook Dim strWkbName As String Dim secAutomation As MsoAutomationSecurity Application.EnableCancelKey = xlInterrupt On Error GoTo 0 'Get macro security level secAutomation = Application.AutomationSecurity 'Get file name strWkbName = Application.GetOpenFilename 'Open file Application.AutomationSecurity = msoAutomationSecurityForceDisable Set wkb = Workbooks.Open(Filename:=strWkbName, UpdateLinks:=2) Debug.Print "Check_1" Application.AutomationSecurity = secAutomation Debug.Print "Check_2" 'Turn off sharing If wkb.MultiUserEditing = True Then Debug.Print "Check_3" Application.DisplayAlerts = False DoEvents Debug.Print "Check_4" 'Application.EnableCancelKey = xlDisabled Debug.Print "Check_5" On Error Resume Next Debug.Print "Check_6" wkb.ExclusiveAccess DoEvents On Error GoTo 0 Debug.Print "Check_7" 'Application.EnableCancelKey = xlInterrupt Debug.Print "Check_8" Application.DisplayAlerts = True Debug.Print "Check_9" End If Debug.Print "Check_10" Stop End Sub