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

  • Thread starter Thread starter sam
  • Start date Start date
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
 
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---
 
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
 
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)
 
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
 
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---
 
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
 
Back
Top