How do I resolve a false "spreadsheet locked for editing" issue?

Question

Using the Office 2010 suite, I have a PowerPoint presentation where all the charts are linked to an Excel workbook. In order to move the presentation and/or workbook to another directory, all the links must be updated to point to the new workbook location. To do so, I've written the following code which resides in a standard code module in PowerPoint:

Private Sub RedirectLinks()

  Dim Source As String
  Dim Dest As String
  Dim Action As Integer
  If InStr(1, ActivePresentation.Path, "Dev\") > 1 Then
    Action = MsgBox("Changing pointers to PRODUCTION", vbOKCancel)
    Source = "Dev\"
    Dest = vbNull
  Else
    Action = MsgBox("Changing pointers to DEVELOPMENT", vbOKCancel)
    Source = "Templates\"
    Dest = "Dev\Templates\"
  End If

  If Action = vbOK Then
    Dim SL As Slide
    Dim SH As Shape
    Dim Top As Double
    Dim Left As Double
    Dim Width As Double
    Dim Height As Double
    For Each SL In ActivePresentation.Slides
      SL.Select
      For Each SH In SL.Shapes
        SH.Select
        If SH.Type = msoLinkedOLEObject Then                                 'when we find a linked one
          Top = SH.Top
          Left = SH.Left
          Width = SH.Width
          Height = SH.Height
          SH.LinkFormat.SourceFullName = Replace(SH.LinkFormat.SourceFullName, Source, Dest)
          SH.Top = Top
          SH.Left = Left
          SH.Height = Height
          SH.Width = Width
        End If
      Next
    Next
  End If

  If InStr(1, Dest, "dev") > 0 Then
    Action = MsgBox("About to OVER WRITE the Dev copy with this one." & vbCrLf & "Click 'Cancel' to prevent this and save manually", vbOKCancel, "OVER WRITE WARNING!!")
  Else
    Action = MsgBox("About to OVER WRITE the PRODUCTION copy with this one." & vbCrLf & "Click 'Cancel' to prevent this and save manually", vbOKCancel, "OVER WRITE WARNING!!")
  End If

  If Action = vbOK Then
    ActivePresentation.SaveAs Replace(ActivePresentation.Path, Source, Dest) & ActivePresentation.Name
  End If

End Sub

The code executes just fine, however, I frequently get this message box popping up from Excel when it is executing the SH.LinkFormat.SourceFullName = Replace(SH.LinkFormat.SourceFullName, Source, Dest) line.

Items of note:

1. The workbook in question is actually closed - I know that it's not open by anyone else (I'm the only one who usually uses it, and the other person who's in there isn't in the office this morning).
2. It claims the file is locked by 'another user' which is actually me. I can often get this warning by closing the workbook, then immediately reopening it. I don't know if it's a network latency issue (file resides on a server, not locally), or what, but after a few moments of using the workbook, I'll get the workbook is now available for read-write message.
3. I don't get this warning every time it tries to execute the line that sets the .SourceFullName. Sometimes I'll get it most times, sometimes I won't get it at all, sometimes I'll get it on occasion.
4. Despite my thoughts of network lag, it doesn't matter how quickly or slowly I debug through the code, I'll get this message at random times.
5. Flagging either new or old workbooks as Read-only at the OS level does not seem to improve the situation.
   • However, flagging both seems to get me 2 warnings for each replacement line execution.

Does anyone have any suggestions on how to resolve this?

Answer 1

I've run into odd behaviors when code in PPT opens a PPTM and my Macro security settings are anything tighter than "Open any fool thing". Try dialing your macros security in PPT and Excel as low as they'll go, just as a test, and see if that eliminates the problem.

If anyone knows of a way to set the security options on the fly and reset them after, that'd be even better. It might be possible to do that via the registry prior to doing anything that'd invoke XL.