Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying data in a sheet to a new Word document problem
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Cell data and pasting into word document? | Excel Programming | |||
Copying an Excel chart into a Word document | Excel Discussion (Misc queries) | |||
Is there an easy way to take data from a spread sheet and have it entered into a Word document? | New Users to Excel | |||
Getting specific data from a word document into an excel sheet | Excel Worksheet Functions | |||
Copying an Excel spreadsheet to a Word document | Excel Discussion (Misc queries) |