VBA: Export Excel data to word document based on word template

[Captain Smith]

I have spent 5 hours on this today and am still at square one. Been all over the internet, but not finding what I need.

From Excel, I want to take a range of data and export it as a new word document, but the new word document needs to be based on an already existing word template.

How do I do this?

Thank you.

 

[Captain Smith]

I used the following code from Daniel in the link below and it works great.

Just need to know how to change this code to create the new word document based on an already
existing .doc or .dot.

http://www.mrexcel.com/forum/showthread.php?t=13891

Thank you.

 

[Cindy Ellis]

I think I can help you with this, but I'm not sure I understand exactly what you're working with. Does the existing Word template (or .doc) have existing content, and you want to add content from Excel to the existing document? Or do you just want the formatting and styles that come along with a template?
Hoping to help,
Cindy

 

[Captain Smith]

Existing template just has the header and footer. I want to throw the Excel data on a blank copy of the template, but on a copy of the template so I don't overlay the original.

Thank you.

 

[Captain Smith]

Existing template just has the header and footer, wtih graphics for each. I want to throw the Excel data on a blank copy of the template, but not on the template itself so that I don't overlay the original. I want my excel data to save as a word document that has the same background header and footer as the template. Daniel's code below does what I want except that it creates a brand new word document without the header and footer graphics that are sitting in my other word template. If I could just change to the code below the third line instead of it being Set WdObj = CreateObject("Word.Application") make it something like Set WdObj = CreateObject("Word.Application").copyfrom("C:\mytemplate") I would be set. I just don't know the syntax to do this.

Thank you so much.

Dim WdObj As Object, fname As String
fname = "Word"
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False
Range("A1:I30").Select
Selection.Copy 'Your Copy Range
WdObj.Documents.Add
WdObj.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
Application.CutCopyMode = False
If fname <> "" Then 'make sure fname is not blank
With WdObj
.ChangeFileOpenDirectory "c:\temp" 'save Dir
.ActiveDocument.SaveAs Filename:=fname & ".doc"
End With
Else:
MsgBox ("File not saved, naming range was botched, guess again.")
End If
With WdObj
.ActiveDocument.Close
.Quit
End With
Set WdObj = Nothing

 

[Cindy Ellis]

If you change

WdObj.Documents.Add

to

WdObj.Documents.Open Filename:="C:\mytemplate.doc"

the macro should do what you need. Saving to "fname" prevents the original from being written over.
Let me know if this works for you,

Cindy

 

[Captain Smith]

YES! That is what I needed.

Thank you so much!!!!

 

[Cindy Ellis]

You're welcome! Glad it was something straightforward

 

[dannyb_ro]

Hello,
I really enjoyed the code mentioned above and for this one i thank you!
But i have another interesting question: Is it possible to collect data from an excel file and place that data scattered through out a word file? The word file contains both characters and numbers. The information extracted from excel is formed of numbers. It's kind of a template which you can automatically change and create from the data stored into the excel file.
Thanks in advance

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
You can use Word's "Mail Merge" to populate certain spots in the Word document with the data from Excel. The data in Excel should be organized with a header in the first row, and the data in rows below the header, with no blank rows. The rest of the specific steps depend on your version of Word. Assuming Office 2007 (the latest that I have access to at the moment), work in a copy of your document (just in case something goes wrong), then choose the Mailings tab, then choose Start Mail Merge, and select "Letters" (other options may work, too, but choose Letters for the time being). This will convert your current document to a mail merge "main" document. You aren't really using this for mailing, so the next step doesn't really have anything to do with recipients...but that's the button you need to choose to do what you want to do. So...select "Choose Recipients", then "Use existing list" Navigate to the folder with your Excel file, and select it. It may ask what sheet or table to use. Select the one with your data .
Then, use the "Insert Merge Field" button to insert the "field" for each data item into the correct spot in the document. You can use "Preview Results" to see the outcome. Save this document, then use "Finish and Merge" to create a new document or documents with the data, or print directly to a printer. Each time you open the main document with the merge fields in it, Word will access the Excel workbook and fill in the info when you either preview or Finish and Merge.
Hope that helps,