title: Reading rich text from Access in to Word
body: <p>I have memo fields in an access 2007 database that are set up as rich text – both in the field and on the form – and I need to get these in to Word and preserve the formatting.  I have seen various solutions on StackOverflow recommending saving to a file (this didn’t format the text) and using IE (error with ExecCommand “copy”), the solution I have found that works for most cases is to use the clipboard, but I can’t get it to work for all types of formatting.</p>
  
  <p>A few more details:  I am connecting to the database from Word using AdoDB, reading the data out in to an array.  From there copy the data to the clipboard and then I use Pastespecial – see solution from Microsoft Support <a href="http://support2.microsoft.com/default.aspx?scid=kb;en-us;274326" rel="nofollow">http://support2.microsoft.com/default.aspx?scid=kb;en-us;274326</a></p>
  
  <p>The code to connect to Access is here (but I believe this is pretty std):</p>
  
  <pre><code>Dim cn As Object
  Dim rs As Object
  Dim strCon As String
  
  'define connection
  Set cn = CreateObject("ADODB.Connection")
  
  'connect string for Access - no password
  strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; strDataBaseFilePath &amp; ";Persist Security Info=False;"
  
  'open connection
  cn.Open strCon
  
  'get recordset
  Set rs = cn.Execute(strSQL)
  
  'load data into array
  LoadDataFromDatabase = rs.GetRows
  </code></pre>
  
  <p>The code to Pastespecial the text in to Word is here:</p>
  
  <pre><code>PutHTMLClipboard CStr(varData)
  rge.PasteSpecial datatype:=WdPasteDataType.wdPasteHTML
  </code></pre>
  
  <p>'PutHTMLClipboard is the Microsoft routine, vardata is defined as a variant and holds the data from one rich text field in Access.</p>
  
  <p>Using this approach I get a few anomalies:</p>
  
  <ol>
  <li><p>text which has custom bullets (e.g. in my situation  ’>’ rather than a standard dot) gets changed to a strange character</p></li>
  <li><p>underlined text is formatted correctly, but if the text is bold + underline then only the bold formatting is set.  Bold + coloured font works OK though.</p></li>
  </ol>
  
  <p>If I manually highlight the text in the Access form and copy and paste it directly in to Word then all the formatting is preserved perfectly.</p>