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

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

On Mar 10, 3:51*am, gbpg wrote:
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