copying data in a sheet to a new Word document problem

[gbpg]

I have been trying to make the below sub work. I keep getting errors of :
Compile error:

can't find project or library

I did go to tools and reference and load Microsoft word 11.0 object library
sheet 1 has 3 columns Regions, SalesNum, SalesAmt




Sub MakeMemos()
'Creates memos in word using Automation
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
Dim Data As Range, message As String
Dim Records As Integer, i As Integer
Dim Region As String, SalesAmt As String, SalesNum As String

'Start Word and create an object (late binding)
Set WordApp = CreateObject("Word.Application")

'Information from worksheet
Set Data = Sheet("Sheet1").Range("A1")
message = Sheet("Sheet1").Range("Message")

'Cycle through all records in Sheet1
Records = Application.CountA(Sheets("Sheet1").Range("A:A"))
For i = 1 To Records
' Update status bar progress message
Application.SatusBar = "Processing Record " & i

' Assign current data to variables
Region = Data.Cells(i, 1).Value
SalesNum = Data.Cells(i, 2).Value
SalesAmt = Format(Data.Cells(i, 3).Value, "#,000")
'Determine the filename
SaveAsName = Application.DefaultFilePath & _
"\" & Region & ".doc"

'Send commands to Word
With WordApp
.Documents.Add
With .Selection
.Font.Size = 14
.Font.Bold = True
.ParagraphFormat.Alignment = 1
.TypeText Text:="MEMORANDUM"
.TypeParagraph
.TypeParagraph
.FontSize = 12
.ParagraphFormat.Alignment = 0
.Font.Bold = False
.TypeText Text:="Date:" & vbTab & _
.Format("mmmm d, yyyy")
.TypeParagraph
.TypeText Text:="To:" & vbTab & Region & _
"Manager"
.TypeParagraph
.TypeText Text:="From:" & vbTab & _
Application.UserName
.TypeParagraph
.TypeParagraph
.TypeText message
.TypeParagraph
.TypeParagraph
.TypeText Text:="Units Sold:" & vbTab & _
"SalesNum"
.TypeParagraph
.TypeText Text:="Amount:" & vbTab & _
Format(SalesAmt, "$#,##0")
End With
.ActiveDocument.SaveAs FileName:=SaveAsName
End With
Next i
'Kill the object
WordApp = Nothing

'Reset status bar
Application.StatusBar = " "
MsgBox Records & "memos were created and stored in " & _
Application.DefaultFilePath
End Sub

 

[paul.robinson]

Hi
You might try being a bit more precise with the code

Dim WordApp as Word.Application
Dim Worddoc as Word.Document

Further on in code try

Set Worddoc = WordApp.document.add
With Worddoc
.Font.Size = 14
.Font.Bold = True
etc
.SaveAs FileName:=SaveAsName
end with

and release objects in right order
set Worddoc = Nothing
set WordApp = Nothing

connecting to Word seems to throw up errors unless you take care. It
can throw up errors even if you do take care (try seraching this
Newsgroup)
regards
Paul