populate word from excel: On clickin "Export" button in excel

S

sam

Hi All, I want to populate a word template with data frome excel. I have
designed an "Export" button in excel sheet, which on clicking should populate
certain fields in a word file. Here is my code so far, But its not working!!
Any help would be highly appretiated.

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)

strTemplateName = "TestTemplate.dot"

strPathTemplateName = "C:\My Documents" & strTemplateName
Set objWord = CreateObject("Word.Application")


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
 
R

ryguy7272

I already answered this question for you. Did you follow the steps? Did you
enter a named range into Excel, and name it 'Address'? Did you create a
DocVariable in Word, and name it 'Address'? Did you add the reference to
Word from Excel? If not, look here for instructions on how to do this:
http://www.cpearson.com/excel/References.htm

You must have missed a step somewhere. i know that's how it is done; I've
done it many times.

Try again...

Goodluck,
Ryan---
 
S

sam

Tried again... I am getting an error "This command is not available because
no document is open" on this line:
objWord.ActiveDocument.variables("Address").Value = Range("Address").Value

Few questions: I have saved the Word file with "Address" field name and done
same in excel with the same name. Should I save the word file at a specific
location? Should I save it as a Word template? or just a normal word file?
Here is my entire code:

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)

Set objWord = CreateObject("Word.Application")

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

objWord.Visible = True

Set objDoc = Nothing
Set objWord = Nothing
End Sub


Thanks in Advance
 
R

ryguy7272

As I recall, you open run the code from Excel, search for the word doc, click
it to open, and the data gets pushed from Excel to Word. If you send me the
two files I'll do it for you. However, you won't learn as much that way.

Ryan--
(e-mail address removed)
 
S

sam

Thank you for all your help, I did work on it and I do understand how it is
done.. But I think I am missing something here, I will send you the files.
Thanks a lot again
 
R

ryguy7272

Check your email. This issue has been resolved. Not sure what the problem
was; maybe the word doc got corrupted. It seemed like you have things set up
right. Anyway, it works fine now.

HTH,
Ryan---
 
S

sam

Hey Ryan, I am not getting any errors with the code now, But the data is
still not exported to word document. Is there anything I am missing?

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