title: Excel changes Link to Add-in
body: <p>I need to link up an .xls (must not have macros) to a macro on our network.  I thought I had found a good solution, which uses a shape to link up to a macro that is set an add-in in Excel.  This method worked well for over a year, but now things are starting to act REALLY ODD.  Excel keeps changing my links from the network to the local drive! </p>
  
  <p>Basically, at my computer I run code that creates a button and sets the .OnAction value to 'SPC_BUTTONS.xlam!EXPORTSPC' which excel sometimes updates to 'G:\NetworkLocation\SPC_BUTTONS.xlam!EXPORTSPC' (sometimes it  leaves it alone).  I linked up the add-in to run from the network (and don't copy it locally) so this is a correct address for it.  However when I move the file from my computer, to the network and on to the final computer, the link will change to 'C:\NetworkLocation\SPC_BUTTONS.xlam!EXPORTSPC'. The file's links work until the point it lands on the local computer. </p>
  
  <pre><code>'Check if SPC Button already exists
  Dim exportButton As Shape
  On Error Resume Next
      Set exportButton = InspectionWS.Shapes("SPC Button")
  On Error GoTo 0
  
  If Not exportButton Is Nothing Then
      'Delete the old button
      InspectionWS.Shapes("SPC Button").Delete
  End If
  
  'Create "SPC Button"
  InspectionWS.Shapes.AddShape(msoShapeRectangle, 770, 600, 160, 36).Name = "SPC Button"
  Set exportButton = InspectionWS.Shapes("SPC Button")
  
  With exportButton
      .ShapeStyle = msoShapeStylePreset34
      .TextFrame2.TextRange.Characters.Text = "Export SPC Data"
      .TextFrame2.TextRange.Font.Size = 20
      .TextFrame2.WordArtformat = msoTextEffect8
      .OnAction = "EXPORTSPC"
  End With
  </code></pre>
  
  <p>The first time I saw this problem was when I had changed the add-in. I had made another macro in that file called EXPORTSPC2, and had swapped names with original when it was time to go live. That's when all of the links became broken.  I swapped back the names and decided to just swap the code, in case position mattered.  Still didn't help with the linking.</p>
  
  <p>I looped the original macro that set up the buttons on every file again, but that failed to rectify the problem. Several hours later, after trying a lot of options I found setting .OnAction to just 'EXPORTSPC' did again get some files to link up again, but ONLY if I did it on the local computer. I had tried the full address, a local address (and moving the add-in location), and SPC_BUTTONS.xlam!EXPORTSPC and of those still ended up with 'C:/Networklocation.xlam!EXPORTSPC'... even when set on the local computer.</p>
  
  <p>The problem is, yesterday, it happened again when someone had replaced the updated files with another set of files he had been modifying on the network.  This time around I wasn't surprised to see that they didn't link up (although disappointed), but was surprised that the my code looping the update to EXPORTSPC failed to 'take' for all of the files on the second computer.  Only a handful of them would actually change to EXPORTSPC, and I ended up having to assign the link locally on the final computer(s), by hand, for each of the 45 .xls files.  </p>
  
  <p>Really, I can't keep doing any of this local repairing, as it creates machine downtime.</p>
  
  <p>So my questions!  Is there a better way to do what I'm trying to accomplish (.xls to add-in)?  Any idea as to why is moving these files causing excel to change the link, forcing me to set them up locally?  Why does it suddenly not accept anything but EXPORTSPC as the link name?  Does excel save some sort of ID for macros/add-in/buttons I'm unaware of I'm accidently breaking? </p>
  
  <p>Really, Any sort of insight will help.  Thanks!  </p>
  
  <p><strong>EDIT:</strong>  </p>
  
  <p>I did find an error in my looping code, which effected the issue I had on the second computer.  It was looping through each workbook fine and saving... but I didn't activate the workbook before running the code, which was pretty important (DOH!).  This only resolves the problem I ran into yesterday where I had to edit by hand.</p>
  
  <p>However, digging a little deeper, I do feel like I have a better understanding.  I used the code below to determine if what I see in 'Assign Macro' matches the stored .OnAction value, which it does not.  Testing on my computer this code:</p>
  
  <pre><code>exportButton.OnAction = "'G:\NetworkLocation\SPC_BUTTONS.xlam!'EXPORTSPC" 
  </code></pre>
  
  <p>would cause the 'Assign Macro' to change to 'EXPORTSPC', even though debugging</p>
  
  <pre><code>debug.print exportButton.OnAction
  </code></pre>
  
  <p>still displayed the original OnAction value.  When I moved the file to the shop floor computer, 'assign macro' would display 'G:\NetworkLocation\SPC_BUTTONS.xlam!'EXPORTSPC until I clicked it, and then it would switch to EXPORTSPC.</p>
  
  <p>So it seems Excel does keep track of where the macro is located once it detects the correct location.  I swapped the macros around and it seemed to have broke the connection.  My guess is excel then modifies the location to the C:\ once it's internal link is broken.  If anyone has any elaboration/verification of this, I'd love to hear it.</p>
  
  <p>I do recall having the add-in button becoming unchecked randomly, which may have been playing into why links kept breaking and reverting to the c:\ while I was debugging what would work.  I'm not sure what was causing that either.  But using the all forms of exporting seemed to work today.  I'm still not convinced that I won't see it again though, since I don't  fully understand what was the catalyst of unchecked boxes and swapped links.</p>
  
  <pre><code>    If Not exportButton Is Nothing Then
          With exportButton
              .OnAction = "'SPC_BUTTONS.xlam'!EXPORTSPC"
              '.OnAction = "EXPORTSPC"
              '.OnAction = "'G:\NetworkLocation\SPC_BUTTONS.xlam'!EXPORTSPC"
          End With
  
          Debug.Print exportButton.OnAction
      End If
  </code></pre>
  
  <p>So parts of the process still seem fuzzy, but at least now I know how to better track what I'm seeing.  </p>