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