PDA

View Full Version : link excel to word letter



coxonitus
05-03-2011, 02:59 AM
hi guys,
i'm searching almost every forum about linking excel en word, but i cannot find really specific things.
The question is.

I have an excel datase of contacts, adresses etc...
i would like to link this to a word letter...
that means if i press a button, an msgbox popsup... which number to choose...( if for example nr 20 is selected)... then the name, adress , etc is selected en pasted automatically in the word letter at the location above the letter.

is it also possible that de msgbox asks me , which document to link to???

is anything possible
if yes,, i would really appreciate it

thnx

Bob Phillips
05-03-2011, 06:07 AM
Do the Word documents have bookmarks where the data is to be inserted?

coxonitus
05-03-2011, 06:52 AM
i think i'm not sure what you mean

Bob Phillips
05-03-2011, 07:21 AM
Google Word bookmarks, you will see what I mean, how helpful they are, and how they would help.

Kenneth Hobs
05-03-2011, 02:31 PM
I am not sure what you mean by link. If you want to do that then link in MSWord to a named range in an Excel file.

It is easy to fill an MSWord file by bookmarks or formfields. Showing a dialog to select an MSWord file is easy enough.

For cells in the active row to an MSWord file example:
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054

'FormFields
'http://www.mrexcel.com/forum/showthread.php?p=1639696

Sub FillForm()
Dim wdApp As Object, WD As Object, rn As Long
Dim myRange As Excel.Range, a() As Variant
Dim s As String

rn = ActiveCell.Row
Set myRange = ThisWorkbook.Worksheets(1).Range("A1", _
Range("E" & Rows.Count).End(xlUp))

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set WD = wdApp.Documents.Open(ThisWorkbook.Path & "\Test.doc")

wdApp.Visible = True
With WD
'.FormFields("Brand").Result = Cells(rn, "B")
'.Bookmarks.Item("Bookmark1").Range.InsertAfter Join(myRange, vbCrLf)
Debug.Print myRange.Address
's = Join(myRange.Value, vbCrLf)
s = "Ken"
TextInBName WD, "Bookmark1", s
End With

Set WD = Nothing
Set wdApp = Nothing
End Sub

'Similar to Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Requires Word Reference
Sub TextInBName(ByRef WDoc As Word.Document, ByVal BName As String, ByVal TextIn As String)
With WDoc
If .Bookmarks.Exists(BName) Then
Dim r As Word.Range
Set r = WDoc.Bookmarks(BName).Range
r.Text = TextIn
WDoc.Bookmarks.Add BName, r
Else
Debug.Print "Bookmark not found: " & BName
End If
End With
End Sub