title: Access VBA Export to Excel with Formats
body: <p>I have Access 2010 linked to some views of a SharePoint 2010 list. The users decided they wanted to change some of the People Picker fields to enable multi-select. This broke the Access automation that I built for reporting.</p>
  
  <p>The issue is when the VBA calls</p>
  
  <pre><code> DoCmd.TransferSpreadsheet
  </code></pre>
  
  <p>What I get in these fields in Excel is</p>
  
  <pre><code> ID | LOB Exec
   1  | 16; 235
  </code></pre>
  
  <p>When I view the query results in Access I get</p>
  
  <pre><code> ID | LOB Exec
   1  | Last, First M, Last, First M
  </code></pre>
  
  <p>For my report obviously they want the latter. If I select Export then Excel the prompt asks if I want to Save with formats. When I do that I get the results I need. I cannot use a saved export procedure because multiple users export to multiple Excel files so I have to be able to tell the export VBA which file to export to. </p>
  
  <p>Any ideas how to duplicate this action with VBA would be greatly appreciated.</p>