Thanks to several responders, I was able to get the XL to Word print working... sort of. For some strange reason, it only works when I run it in the debugger. If I invoke it using "Alt-F8", the macro appears to work, but the Word page never prints. If I single-step it through the debugger,
it works as advertised. This is XL2000 under XP Pro SP2 with 256mb memory.
I am attaching the workbook, with the macro source and test data. Since there is a one attachment limit, I will post again with the Word document template. Please remember to update the code to point to the template.
The basic problem is that I get no output from Word if I just run the macro. If I run the macro in the debugger using F8 "single step", it works as designed. To use this macro, you must SELECT a row (the one to print) BEFORE you invoke "prtOneRecap".
FYI... All data is FICTIONAL.
Perhaps some of you can spot my blunder in the code, so you can take a look here:
[vba]
Sub prtOneRecap()
'
' prtOneRecap Prints one recap sheet. Code is basically the
' same as prtRecap, except that it prints only the
' selected row.
Dim rcMsg As Integer
Dim i As Integer
Dim wrdApp As Word.Application
Dim rngDoc As Word.Range
Dim wrdDoc As Word.Document
Dim sPropName As String
Dim sMoYr As String
Dim sInitials As String
Dim sTotalOpIncome As String
Dim sTotalGrossIncome As String
Dim sGrossPotRent As String
Dim sConcessions As String
Dim sNetIncome As String
Dim sActPercent As String
Dim sPrePaid As String
Dim sFutureRent As String
Dim sAdjIncomeMonth As String
Dim sAdjPercent As String
Dim sDelinq As String
Dim sPastDue As String
On Error GoTo errHandler
rcMsg = MsgBox("prtOneRecap Version1.0")
Set CurrentRange = Selection
i = CurrentRange.Row
Set wrdApp = New Word.Application
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open(FileName:="c:\chuck\New Monthly Recap.dot")
Set rngDoc = wrdApp.ActiveDocument.Range(Start:=0, End:=400)
'
' Repair the printable data.
' this means Word was printing "12345"
' when we wannted $12,345. There was
' also a problem with dates and percentages
'
sPropName = StrFix(Cells(i, 1), 44)
sMoYr = StrFix(Format(Cells(i, 2), "mmm yy"), 12)
sTotalOpIncome = StrFix(Format(Cells(i, 9), "$###,##0"), 20)
sTotalGrossIncome = StrFix(Format(Cells(i, 10), "$###,##0"), 20)
sGrossPotRent = StrFix(Format(Cells(i, 3), "$###,##0"), 20)
sConcessions = StrFix(Format(Cells(i, 5), "$####,###0"), 20)
sNetIncome = StrFix(Format(Cells(i, 11), "$###,##0"), 20)
sActPercent = StrFix(Format(Cells(i, 13), "##.0#%"), 12)
'sTotalOpIncome2 = sTotalOpIncome
sPrePaid = StrFix(Format(Cells(i, 4), "$###,##0"), 12)
sFutureRent = StrFix(Format(Cells(i, 7), "$###,##0"), 12)
sAdjIncomeMonth = StrFix(Format(Cells(i, 12), "$###,##0"), 20)
sAdjPercent = StrFix(Format(Cells(i, 14), "##.0#%"), 12)
sDelinq = StrFix(Format(Cells(i, 6), "$###,##0"), 20)
sPastDue = StrFix(Format(Cells(i, 8), "$###,##0"), 20)
With wrdApp.ActiveDocument
.Bookmarks("PropName").Range.Text = sPropName 'header line
.Bookmarks("MoYr").Range.Text = sMoYr
.Bookmarks("TotalOpIncome").Range.Text = sTotalOpIncome 'line 1
.Bookmarks("TotalGrossIncome").Range.Text = sTotalGrossIncome
.Bookmarks("GrossPotRent").Range.Text = sGrossPotRent 'line 2
.Bookmarks("Concessions").Range.Text = sConcessions
.Bookmarks("NetIncome").Range.Text = sNetIncome
.Bookmarks("ActPercent").Range.Text = sActPercent 'line 3
.Bookmarks("TotalOpIncome2").Range.Text = sTotalOpIncome 'line 5
.Bookmarks("PrePaid").Range.Text = sPrePaid
.Bookmarks("FutureRent").Range.Text = sFutureRent 'line 6
.Bookmarks("AdjIncomeMonth").Range.Text = sAdjIncomeMonth
.Bookmarks("AdjPercent").Range.Text = sAdjPercent
.Bookmarks("Delinq").Range.Text = sDelinq 'line 7
.Bookmarks("PastDue").Range.Text = sPastDue
End With
wrdApp.Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Set rngDoc = Nothing 'Clean up Range
Set wrdDoc = Nothing
wrdApp.Quit savechanges:=False
Set wrdApp = Nothing
GoTo endIt
errHandler:
rcMsg = MsgBox("prtOneRecap- The error handler was entered!")
Set rngDoc = Nothing
Set wrdDoc = Nothing
wrdApp.Quit savechanges:=False
Set wrdApp = Nothing
rcMsg = MsgBox("The error handler issued Quit!")
endIt:
End Sub [/vba]
I am interested in general comments on this code as well since I am very new to this genre.