title: XML Exporting and Mapping in VBA for Excel
body: <p>Wall of text incoming, beware.</p>
  
  <p>I have a spreadsheet with, of particular importance to these functions, a number of links to a web document.  These links have an address (the base URL), a sub-address (#page=, which needs to be in the subaddress so Excel doesn't get confused by the #), and a display text which is the page number.  This spreadsheet has an XML map which someone else made.  </p>
  
  <p>If I export the spreadsheet to XML normally, it exports <em>only</em> the display text of a link (so, the page number, but not the actual URL).  I want the XML data to save the link rather than the display text.  </p>
  
  <p>The method I chose for this was to use the BeforeXMLExport and AfterXMLExport to </p>
  
  <ol>
  <li>Copy the spreadsheet </li>
  <li>Loop through the spreadsheet, changing the values of each cell with a hyperlink to the actual URL, rather than the display text </li>
  <li>Export </li>
  <li>Delete the changed spreadsheet </li>
  <li>Give the copy spreadsheet the original's name and finally </li>
  <li>Reapply the XML map to the spreadsheet, since it loses it during the copying process.</li>
  </ol>
  
  <p>The main issue with this is that, since there are two spreadsheets at the time of actually importing, my XML file has a bunch of useless data - it is also exporting the spreadsheet that includes display text instead of URL.  Would copying to a different workbook, then moving that sheet to the original book avoid this issue?  If so, how would I go about that?</p>
  
  <p>On a less important note, I do not think this is the most efficient method of doing this.  My particular concerns are </p>
  
  <p>1) Deleting and renaming the sheets seems unnecessarily complicated, but I was not able to get the Worksheet.Copy and Worksheet.Paste functions to play nice with each other </p>
  
  <p>2) Remapping the changed sheet at the end seems unnecessary.  Is there a way to copy a spreadsheet while still retaining the XML map?  Alternatively, I currently just re-map by using a recorded macro.  Is this possible programmatically through VBA?</p>
  
  <p>Here is my current code</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
      Worksheets("Input Form").Copy After:=Worksheets("Input Form") 'Copies worksheet as it is prior to exporting
      Sheets("Input Form").Activate
  
      Dim cl As Range
      For Each cl In Range("A1:BD4") 'Loops through worksheet.  The range would have to be changed later on
          If cl.Hyperlinks.Count &lt;&gt; 0 Then 'Checks cells for a link
              cl.Value = cl.Hyperlinks(1).Address &amp; cl.Hyperlinks(1).SubAddress 'If the cell has a link, change the value of the cell to its hyperlink
          End If
      Next cl
  End Sub
  
  Private Sub Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)
      Application.DisplayAlerts = False 'For sheet deletion
      Application.ScreenUpdating = False 'Efficiency, hide ugly workaround
  
      Worksheets("Input Form").Delete 'Deletes changed worksheet
      Worksheets("Input Form (2)").Name = "Input Form" 'Changes the name of the copy into that of the original
  
      Application.Run "MapXML" 'Copying seems to lose the XML map.  This macro, which I recorded manually, exists to remap.  There is probably a way to do this through VBA, although I have yet to find it.
  
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
  End Sub
  </code></pre>