# 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 


		Code:
__


WdObj.Documents.Add

to 


		Code:
__


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,


----------



## 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.


----------



## dannyb_ro

Brilliant! Thank you! I will try to do so. I'm a structural engineer and it surely is an usefull info!


----------



## didijaba

Thanks for your advice, you have helped me a lot . Thanks.


----------



## sharm94

Hey cindy what if using the mail merge that there is a variable in the main of the letter i.e i have different courses for the same person using mail merge will only show one course they have done


----------



## DarC69

Cindy,

I found your previous answers very helpful and the mail merge side of things works fine.
However I was wondering is it possible to populate template letters (2-3 pages) with data via VBA without having to use Mail Merge. 
I was looking to see if its possible to get a GUI interface I build in Excel to populate these letter templates for each record in my spreadsheet and ideally be able to save and print them individually.

Thanks in advance for any light you can shine on this.
DarC69


----------



## kelas86

DarC69 said:


> Cindy,
> 
> I found your previous answers very helpful and the mail merge side of things works fine.
> However I was wondering is it possible to populate template letters (2-3 pages) with data via VBA without having to use Mail Merge.
> I was looking to see if its possible to get a GUI interface I build in Excel to populate these letter templates for each record in my spreadsheet and ideally be able to save and print them individually.
> 
> Thanks in advance for any light you can shine on this.
> DarC69



Hello everyone,

I'm looking for the simillar solution as DarC69 is looking for. I have a word document (template). It's some type of contract, so it's a multiple page document with a dozens of "text area or forms" or how to call them and I want to fill them using data from Excel via VBA. Using mail merge is fine, but is there any simpler sollution for the final user?

Let's think that the user doesn't know Word very well and I'm looking for the simplest way no matter how difficult is to write the code and put it all together. In the final it should work like this.
1) There is a contract template in Word
2) User opens Excel and fill in specific cells - he/she workes with the common form. 
3) After filling in all necessary cells user just calls the macro (via button probably as he/she even doesn't know what the VBA is actually) and document is automatically fill in and save with a specific name in a specific folder etc.

Since now I have been working just in Excel, having all templates in Excel a printing to PDF documents. I would like to move forward using automatically filled in Word documents. 

Thanks in advance


----------



## Varsha Saxena

Hello,
I want to export data from Excel sheet to word document of existing template.
I don't have any idea about coding.
Can anyone please expain step wise how to proceed?


----------



## Hawkz

Cindy Ellis said:


> You're welcome!  Glad it was something straightforward



Hi, wonderfully explained and it did save a lot of time of mines. Just that now with the above explanation I am not able to properly align my data in word. Which means though my data (series of columns) does get copied from excel to word but it truncates few columns. Can you please suggest. Thanks.


----------



## Macropod

The process outlined in the discussion you responded to should output all the data to Word. There is nothing in the code that would cause data truncation - I copied a range that included a cell with nearly 1800 characters. That said, the code could be made more efficient:
1. There is no need to select an Excel range to copy it or a Word range to paste
2. There is no need to change folders to save - simply specify the path as part of the save parameters.

The following implementation shows how you might create & save a new document with the same filename (different extension) as the workbook. I've included commented-out code showing how the new document might be based on a 'template' stored in the same folder as the workbook. For testing purposes also I've commented-out the code that saves & closes the document and quits Word. The Word session is also made visible so you can access it.


		Code:
__


Sub Demo()
Dim WdApp As New Word.Application, wdDoc As Word.Document, StrDocNm As String
ActiveSheet.Range("A1:J10").Copy
StrDocNm = Split(ActiveWorkbook.FullName, ".xls")(0)
With WdApp
  .Visible = True ' False to hide the Word session
  Set wdDoc = .Documents.Add '(Template:=ActiveWorkbook.Path & "\Template.doc", Visible:=False)
  With wdDoc
    .Range.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
    ' For other save formats, see WdSaveFormat in Word's VBA Help - change the file extension to suit.
    '.SaveAs2 Filename:=StrDocNm & ".doc", FileFormat:=wdFormatDocument97, AddToRecentFiles:=False
    '.Close
  End With
  '.Quit
End With
Set wdDoc = Nothing: Set WdApp = Nothing
Application.CutCopyMode = False
End Sub


----------



## JohnBB

Hello, please excuse the slight hijack but Macropod you have "ActiveSheet.Range("A1:J10").Copy", what if I want to copy multiple worksheets? I am already doing this as a multi-page "create pdf", I just want to create a .doc instead. Thank you


----------



## Macropod

Obviously, then, you'd need to implement a loop that iteratively copies and pastes the various ranges to the Word document. The code structure would thus be quite different. If you need help with that, you should start a new thread.


----------

