Copy text from access form to a word Document

[newbie83]

Hi,

im sure that this is even possible, as i have search the internet high and low for a solution.

i am just wondering if it is at all possible to copy the inserted text from an access Form and paste on to a word document, ideally using a marco attached to a button?

any help on this or even a point in the right direction will be extremely appreciated.

Thank you

[jonh]

Outputting the text is pretty simple.

Public Sub write_to_word(frm as form)
    Dim WA As Word.Application
    Dim WD As Word.Document
    Dim ctl As Control
    
    Set WA = CreateObject("Word.Application")
    Set WD = WA.Documents.Add(, , , True)
    WA.Visible = True
    WA.Activate
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox: WA.Selection.InsertAfter ctl.Value  & vbCrLf
        End Select
    Next
End Sub

Put that in a new module.
Call it from anywhere and pass in a form. e.g.

write_to_word me
or
write_to_word form1

[newbie83]

Hi Jonh,

i dont think i have explained what im trying to do very well, i have attached a screenshot of the form so you can take a look.

i need to copy the form text and format and paste to a word doc so that i can email the information to a client. however the form/document cannot be an attachment it must be (send mail to recipient) style.

[jonh]

I've added the label captions and control values into a 2 column table and added some formatting.

Pass in the form and title control, e.g.

write_to_word Me, lblTitle

Public Sub write_to_word(frm As Form, ttl As Control)
    Dim WA As New Word.Application
    Dim WD As Word.Document
    Dim ctl As Control
    Dim r As Word.Row
    Dim t As Word.Table

    Set WD = WA.Documents.Add(, , , True)
    WA.Visible = True
    WA.Activate
    
    Set t = WD.Tables.Add(WA.Selection.Range, 1, 2)
    t.Columns(1).Shading.BackgroundPatternColor = wdColorGray125
    t.Columns(1).Width = 80
    t.Columns(2).Width = 325
    
    For Each ctl In frm.Controls
        If ctl.Name <> ttl.Name Then
            Select Case ctl.ControlType
                Case acLabel
                    If ctl.Parent.Name = Me.Name Then
                        tadd t, ctl, Nothing
                    End If
                Case acCommandButton
                Case Else
                    If Not ctl.Controls(0) Is Nothing Then
                        tadd t, ctl.Controls(0), ctl
                    Else
                        tadd t, Nothing, ctl
                    End If
            End Select
        End If
    Next
    
    With t.Rows(1)
        .Cells.Merge
        Select Case ttl.ControlType
        Case acLabel
            .Cells(1).Range.Text = ttl.Caption
        Case Else
            .Cells(1).Range.Text = ttl.Value
        End Select
        .Shading.BackgroundPatternColor = wdColorWhite
        .Range.Style = ActiveDocument.Styles("Title")
        .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End With
End Sub

Sub tadd(t As Word.Table, lbl As Label, ctl As Control)
    Dim r As Word.Row
    Set r = t.Rows.Add
    If Not lbl Is Nothing Then
        r.Cells(1).Range.Text = lbl.Caption
        r.Cells(1).Range.Font.Name = lbl.FontName
        r.Cells(1).Range.Font.Bold = lbl.FontBold
        r.Cells(1).Range.Font.Size = lbl.FontSize
    End If
    If Not ctl Is Nothing Then
        r.Cells(2).Range.Text = "" & ctl.Value
        r.Cells(2).Range.Font.Name = ctl.FontName
        r.Cells(2).Range.Font.Bold = ctl.FontBold
        r.Cells(2).Range.Font.Size = ctl.FontSize
    End If
End Sub