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 <g...@discussions.microsoft.com> 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
|
Similar Threads
