使用VBA将格式化的文本复制到Access中 [英] copy formatted text into access using vba

问题描述

我需要将Word中的格式化文本保存在Access数据库中.

I need to save formatted text from Word in an Access Database.

到目前为止，我已经设法弄清楚了如何在访问字段中存储格式化的文本(在表中创建备注字段并将文本格式设置为RTF).正在搜索SO，关于如何将所说的文本从单词传输到Access中，我还没有找到解决方案.

So far I've managed to figure out how to store formatted text in an Access Field (Create a Memo Field in a Table and set the Text Format as Rich Text). Searching SO I have not yet come across a solution as to how to transport said text from word into Access.

我知道这是有可能的，因为您可以通过手动复制和粘贴信息来做到这一点.

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

我的问题是，如何使用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

更新 我尝试实施Matt Hall的答案.虽然文本确实已复制到数据库，但尚未保留格式:

UPDATE I tried implementing the answer from Matt Hall. While the text is indeed copied to the database, it does not yet keep 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

推荐答案

以下是大量引用在开始之前，请确保已在VBA编辑器>工具">参考"中选中了这些(或Access版本的等效项)参考:

Before you start make sure you have these (or your Access version's equivalent) references ticked in VBA editor > Tools > References:

Microsoft Word 15.0对象库

Microsoft Word 15.0 Object Library

Microsoft Office 15.0对象库

Microsoft Office 15.0 Object Library

假设您已建立带有命令按钮的表单以触发此MS-Word导入，请将以下函数和子例程放在该表单的VBA模块中的某个位置:

Assuming you've set up a form with a command button to trigger this MS-Word import, put the following function and subroutine somewhere in that form's VBA module:

1)文件选择器功能:

这将使您可以使用在整个Windows中看到的旧的熟悉的文件对话框窗口来选择要使用的MS-Word文档.最终，它所做的只是保存您在(2)中所述的子例程中选择使用的文件的文件路径和文件名...

This will allow you to select the MS-Word Document you want to using the old familiar file dialogue window you see throughout Windows. Ultimately, all it does is save the file path and name of the file you've picked for use in in the subroutine 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)获取MS-Word文档子例程的格式化文本内容:

此子例程将使用在文件选择器"功能(如上)中选择的MS-Word文档的文件路径和名称来打开MS-Word文档，选择所有文本，将其复制到剪贴板，并将其粘贴到在Access中打开表单上的文本框，然后关闭MS-Word ...

This subroutine will use the file path and name of the MS-Word Document selected in the File Picker function (above) to open the MS-Word document, select all the text, copy it to the clipboard, paste it to a text box on 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

您的命令按钮的单击事件:

此子例程应从命令按钮的单击事件运行.它实质上是调用FileToOpen函数和GetWordContent子例程，以便用户选择MS-Word文档，然后让VBA将MS-Word文档中的格式化文本复制并粘贴到以下格式的富文本备注文本框中: Access中的打开表单.

This subroutine should be run from your command button's on-click event. It essentially calls FileToOpen function and the GetWordContent subroutine in order for the user to select a MS-Word Document and then let the VBA copy and paste the formatted text from the MS-Word Document in to a rich text memo textbox on the open form in Access.

请注意，此子例程进行一些假设，并引用控件/表/字段的名称以及可能尚未设置的名称.这些假设是:

Note that this subroutine makes some assumptions, and refers to names of controls/tables/fields and whatnot that you might not have already setup. These assumptions are:

1. 您表单的命令按钮称为 cmdGetWordData
2. 您的Access数据库有一个名为 tblWordDump
的表
3. 您的表单已绑定到表 tblWordDump
4. tblWordDump具有2个备忘录文本字段，分别称为 WordDocDataSrc 和 WordDocData ，用于分别存储导入的文件路径/名称和文本内容，并且两者都添加到您的表单中

1. Your form's command button is called cmdGetWordData
2. Your Access database has a table called tblWordDump
3. Your form is bound to the table tblWordDump
4. tblWordDump has 2 memo text fields called WordDocDataSrc and WordDocData to store the imported file path/name and text contents 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

此处是示例访问文件你戳一下.

这篇关于使用VBA将格式化的文本复制到Access中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！