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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.