copying data in a sheet to a new Word document problem
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
|