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.

 

[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]

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]

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.

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.