Copy formatted text to access using vba

Question

I need to save formatted text from Word to an Access database.

So far, I have managed to figure out how to save formatted text in an access field (create a Memo field in the table and set the text format to Rich Text). Finding SO I have not yet found a solution on how to wrap specified text from a word in Access.

I know that this is possible because you can do this by simply copying and pasting the information if you do it manually.

My question is: how can I copy formatted text from a word to a field in a table using VBA?

Experimentally, I created the following to test this. So far without success ...

Sub GetComments() 'Imports Analyst Comments from Excel files als OLE Objects. '--------------------------------- 'Access Variables Dim dbsFundDB As DAO.Database Dim rsComments As DAO.Recordset Set dbsFundDB = CurrentDb Set rsComments = dbsFundDB.OpenRecordset("tblFunds") 'Word Variables Dim doc As Word.Application Dim dcmt As Word.Document Dim sectn As Word.Section Dim obCommentText As Object Dim sAnalystText As String 'Open New Word File Set doc = New Word.Application doc.Visible = True Set dcmt = doc.Documents.Open(sPathTemplate) Set sectn = dcmt.Sections(1) sectn.Range.Select Selection.InsertFile FileName:="myfile.rtf", Range:="", _ ConfirmConversions:=False, Link:=False, Attachment:=False sAnalystText = sectn.Range.Tables(1).cell(1, 1).Range.FormattedText rsComments.AddNew rsComments![Long Comment Exec] = sAnalystText rsComments.Update sectn.Range.Select dcmt.Close savechanges:=False doc.Quit End Sub 

UPDATE I tried to execute a response from Matt Hall. Although the text was actually copied to the database, it has not yet retained the formatting:

Here is my implementation as a simple test:

 Option Explicit Public Const sPathTemplate As String = "W:\L\BDTP\Products\FundResearchTool\Advisory.docx" Option Compare Database Sub GetComments() 'Imports Comments from word and save in DB 'Test soubroutine '--------------------------------- 'Word Variables Dim obCommentText As Variant Dim strSQL As String obCommentText = GetWordContent(sPathTemplate) strSQL = "insert into [tblText]([TestField]) values('" & obCommentText & "')" DoCmd.RunSQL strSQL MsgBox "Import Successful", vbInformation Or vbOKOnly End Sub Private Function GetWordContent(strFile As String) As Variant ' This function takes the path obtained to the MS-Word Document selected in ' the FileToOpen function and then uses that to open that MS-Word Document ' and retrieve its text contents Dim objDoc As Word.Document Set objDoc = GetObject(strFile) GetWordContent = CVar(objDoc.Sections(1).Range.Text) objDoc.Close End Function 

Answer 1

Here is a method that strongly references this .

Before you begin, make sure you have these (or the equivalent of your version) links marked in the VBA> Tools> Links editor:

Microsoft Word 15.0 Object Library

Microsoft Office 15.0 Object Library

Assuming you created a form using the command button to start this MS-Word import, place the following function and routine somewhere in this form's VBA module:

1) File selection function:

This will allow you to select the MS-Word document that you want to use in the old familiar file dialog box that you see on Windows. Ultimately, all he does is save the path to the file and the name of the file that you selected for use in the routine described in (2) ...

 Private Function FileToOpen() As String ' This function will essentially allow you to browse to MS-Word document ' and then store the path of that file for use in the GetWordContent function Dim fDialog As Office.FileDialog Dim varFile As Variant Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .AllowMultiSelect = False .Title = "Select Word document to import" .Filters.Clear .Filters.Add "Word files", "*.doc?" If _ .Show = True _ Then For Each varFile In .SelectedItems FileToOpen = varFile Next Else FileToOpen = "" End If End With End Function 

2) Get formatted text Contents of the MS-Word routine:

This routine will use the path to the file and the name of the MS-Word document selected in the File Picker function (see above) to open the MS-Word document, select all the text, copy it to the clipboard, paste it into the text field in an open form in Access and then close MS-Word ...

 Private Sub GetWordContent(strFile As String) ' This function takes the path obtained to the MS-Word Document selected in ' the FileToOpen function and then uses that to open that MS-Word Document ' and retrieve its text contents and paste them in to WordDocData textbox on ' the currently open form in Access ' Create an MS-Word Object: Dim objDoc As Object Set objDoc = CreateObject("Word.Application") ' Open the file selected in FileToOpen() and copy the contents to clipboard: With objDoc .Documents.Open strFile .Visible = True .Activate .Selection.WholeStory .Selection.Copy End With ' Set the focus to the WordDocData textbox on the Access Form and paste clipboard: Me.WordDocData.SetFocus DoCmd.RunCommand acCmdPaste Me.WordDocDataSrc = strFile ' Save record on the form: If _ Me.Dirty _ Then Me.Dirty = False End If ' A bit hacky this bit. When you close MS-Word after copying a lot of data, ' you might get a message asking you if you if you want to keep the last item ' you copied. This essentially overwrites the clipboard that currently has ' the whole document stored, to just the first 5 characters, which should allow ' MS-Word to be closed here without a pop-up message to deal with: With objDoc .Selection.HomeKey Unit:=wdLine .Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend .Selection.Copy .Documents.Close .Quit End With Set objDoc = Nothing End Sub 

Button "Team". Click "Event":

This routine should be launched from your command button when you click on an event. It essentially calls the FileToOpen function and the GetWordContent subroutine GetWordContent that the user can select the MS-Word document, and then allow VBA to copy and paste the formatted text from the MS-Word document into the text field with an extended text reminder for an open form in Access.

Note that this routine makes some assumptions and refers to the names of the controls / tables / fields and what you may not have configured already. These assumptions:

• The form command button is called cmdGetWordData strong>
• Your Access database has a table called tblWordDump
• Your form is attached to the tblWordDump table
• tblWordDump has 2 text fields labeled WordDocDataSrc and WordDocData strong> to save the imported path / file name and text content respectively, and both are added to your form

 Private Sub cmdGetWordData_Click() ' This subroutine runs on your command button; it will call both the FileToOpen function and GetWordContent subroutine ' to retrieve the text contents of your chosen MS-Word Document. ' It will then store both the path the text contents of of your chosen MS-Word Document in 2 fields in a table in Access. ' NOTE: this code assumes that your Access database has: ' - a table called tblWordDump ' - a memo text field in this table called WordDocDataSrc to store the path of MS-Word file imported ' - a memo text field in this table called WordDocData with the TextFormat property set to "Rich Text", ' which will store the text and text formating of the MS-Word file imported Dim strFile As String Dim strWordContent As Variant ' Select file via File Dialogue strFile = FileToOpen ' Conditionals when a file was or wasn't selected If _ Len(strFile) > 0 _ Then DoCmd.GoToRecord , , acNewRec GetWordContent strFile MsgBox "Import Successful", vbInformation Or vbOKOnly Else MsgBox "No File Selected", vbExclamation Or vbOKOnly End If End Sub 

Here is an example of this access file so you can pop.