PDA

View Full Version : Solved: Write to Word from Excel



MonteCristo
07-28-2010, 01:49 PM
Hello guys,

I need another favour to ask of you.

I have a program running in Excel 2007 and I'd like it from time to time to send and store reports to a Word file, which gets opened when the user wants to view the results. All is well and nice, except I can't seem to figure out how to write to a doc from Excel. Could you provide an example of a sub that opens Word and writes a sentence? As well, I'm thinking of adding new reports on new lines...

Thank you.

Simon Lloyd
07-28-2010, 02:37 PM
This sin't mine but a well written example :)
Sub MakeWordFile()
' Creates Word document of Auction Items using Automation
Dim WordApp As Object

' Start Word and create an object
Set WordApp = CreateObject("Word.Application")
With WordApp
.Documents.Add
End With

' Determine the file name
SaveAsName = ThisWorkbook.Path & "" & "Auction Catalog.doc"

' Sort Worksheet into proper catalog order
Sheets("Items").Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Key1:=Range("C2"), Order3:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select

' Information from worksheet
Set Data = Sheets("Items").Range("A1")

' Cycle through all records in Items
Records = Application.CountA(Sheets("Items").Range("Title"))
' Records = 7

For i = 2 To Records
' Update status bar progress message
Application.StatusBar = "Processing Record " & i & " of " & Records

' Assign current data to variables
Item1 = Data.Offset(i - 1, 0).Value
Item2 = Data.Offset(i - 1, 1).Value
Title = Data.Offset(i - 1, 2).Value
Descript = Data.Offset(i - 1, 3).Value

' Send commands to Word
With WordApp
With .Selection
.TypeParagraph
.Font.Size = 12
.Font.Bold = True
.ParagraphFormat.Alignment = 0
.TypeText Text:=Item1 & Item2 & ". "
.Font.Underline = True
.TypeText Text:=Title
.Font.Bold = False
.Font.Underline = False
.TypeParagraph
.TypeText Text:=Descript
.TypeParagraph
End With
End With
Next i

' Save the Word file and close it
With WordApp
.ActiveDocument.SaveAs FileName:=SaveAsName
.ActiveWindow.Close
' Kill the object
.Quit
End With

Set WordApp = Nothing

' Reset status bar
Application.StatusBar = ""
MsgBox "Auction Catalog.doc was created and saved in " & ThisWorkbook.Path
End Sub

MonteCristo
07-28-2010, 07:47 PM
Thank you, thank you. I only needed a little bit from that code, but now I understand how to play around with Word from Excel. Cool!