Write Text to Word From Excel using VBA

This program opens a word file and writes text into it and customizes the text slightly.

Explanation

The VBA program opens an already existing word file stored on a hard drive and writes text into the file and makes the text bold etc. Afterwards the file is saved and closed and stored at the same location. This function can be used when making own programs for making customized quotations for example when the existing business system is not sufficient. Almost all customization that can be done with the word file is possible to perform using VBA or the template can be customized before writing text to the file.

In order to make the program work the reference “Microsoft Word XX.X Object Library” needs to be enabled.

An example file of the VBA code is available for downloading at the end of this page, enjoy! Or just copy and paste the code directly from this web page.

Code

Public Sub Write_Text_to_Word_From_Excel_using_VBA()

Dim Write_Text_to_Word_From_Excel_using_VBA_APP As Word.Application
Dim Write_Text_to_Word_From_Excel_using_VBA_DOC As Word.Document
Set Write_Text_to_Word_From_Excel_using_VBA_APP = CreateObject("Word.Application")

Dim PlaceOfWordFile As String
Dim NameOfWordFile As String

PlaceOfWordFile = Range("B4").Value
NameOfWordFile = Range("B5").Value

NamePlace = PlaceOfWordFile + "\" + NameOfWordFile

Write_Text_to_Word_From_Excel_using_VBA_APP.Visible = True

Set Write_Text_to_Word_From_Excel_using_VBA_DOC = Write_Text_to_Word_From_Excel_using_VBA_APP.Documents.Open(NamePlace, ReadOnly:=False)

Row = 0
While Range("B8").Offset(Row, 0).Value <> tom
    Write_Text_to_Word_From_Excel_using_VBA_APP.Selection.Font.Name = Range("B8").Offset(Row, 2).Value
    Write_Text_to_Word_From_Excel_using_VBA_APP.Selection.Font.Size = Range("B8").Offset(Row, 1).Value
    Write_Text_to_Word_From_Excel_using_VBA_APP.Selection.TypeText Text:=Range("B8").Offset(Row, 0).Value
    Write_Text_to_Word_From_Excel_using_VBA_APP.Selection.TypeParagraph
    Row = Row + 1
Wend

Write_Text_to_Word_From_Excel_using_VBA_DOC.Save
Write_Text_to_Word_From_Excel_using_VBA_APP.Quit

Set Write_Text_to_Word_From_Excel_using_VBA_DOC = Nothing
Set Write_Text_to_Word_From_Excel_using_VBA_APP = Nothing
End Sub

Download excel file! Write_Text_to_Word_From_Excel_using_VBA.xls

Add your comment

yvComment v.CommentVersion