export certain cells from excel to word

S

sam

Hi All, I am trying to export certain detail from excel to word on a click of
a button from excel, Here is my code. I know I am missing something, Can you
please guide my way thought this.

Private Sub Export_Click()

Dim objWord As Object 'Word Object
Dim strTemplateName As String 'Template name
Dim strPathTemplateName As String 'Path & Template
Dim sWdFileName As String

Set doc = objWord.Documents.Open(sWdFileName)

strTemplateName = "TestTemplate.dot"

strPathTemplateName = "C:\My Documents" & strTemplateName

Set objWord = CreateObject("Word.Application")

With objWord

.ActiveDocument.bookmarks("Address").Range = Sheet2.Range("B4").Value
.ActiveDocument.bookmarks("City").Range = Sheet2.Range("B5").Value
.ActiveDocument.bookmarks("State").Range = Sheet2.Range("B6").Value
.ActiveDocument.bookmarks("Zip").Range = Sheet2.Range("B7").Value

End With
objWord.Visible = True

Set objDoc = Nothing
Set objWord = Nothing
End Sub

Thanks in Advance
 
R

ryguy7272

Here's how I do it. This code goes in Excel:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

objWord.activedocument.variables("BrokerFirstName").Value =
Range("FirstName").Value
objWord.activedocument.variables("BrokerLastName").Value =
Range("LastName").Value

ActiveDocument.Fields.Update

objWord.Visible = True

End Sub

Set your named ranges in Excel (FirstName and LastName, in this example).
Make sure you set a reference to Word, in Excel. Tools > References > MS
Word. Now in Word, Insert > Field > DocVariable and variable name must match
your named ranges in Excel (FirstName and LastName, in this example). Now,
run your code from Excel.

HTH,
Ryan---
 
S

sam

Hey ryan, Thanks for your help. I am getting a type missmatch error in my
code.
I selected the word reference in Tools---> References
I renamed my references in word and excel to "Address"
Can you please try and find whats wrong with my code, or if anything that I
am missing:

Private Sub Export_Click()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)

With objWord

.ActiveDocument.variables("Address").Value = Range("Address").Value

End With
objWord.Visible = True

Set objDoc = Nothing
Set objWord = Nothing
End Sub

Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top