OutPut to Word Using [ProjectDesc] files as output file name

[RockyStone]

I have a report that I need to output to Microsoft Word. I can output the report to word just fine but what I'm looking to do is have the output to word take the [ProjectDesc] field and use it for the Word file name, loop through the records and produce separate output to Word reports matching the [ProjectDesc] in the table. Any help is appreciated.

 

[ChrisCalvert]

That worked exactly. Thanks! Any idea why it works like that? Seems a bit odd.


-Chris

 

[ChrisCalvert]

errrrrr.. Sorry That is me milti-tasking and working on two posts at once. What I meant was:

Use this : (This assumes Office 2k, but may work elsewhere)
-------------------------------------------------------
Dim stFullFilePath as String


FullFilePath = "Everything but the file name" & _[ProjectDesc] & ".doc"

objWord.Application.ActiveDocument.SaveAs (stFullFilePath)
-----------------------------------------------------
There are a lot of arguments, but filename is first. If you need more (such as how to call and loop through a recordset with ADO), let me know and I will try to put something together. Also, make sure you reference Word 9 object library.


Chris

 

[RockyStone]

Chris, appreciate the reply.. Was hoping for the easy way out by using code already written. If you have something it would be appreciated..

 

[RockyStone]

Ok Chris,
What Im trying to accomplish: (1)Open a report that is pulling the data from a table "TmpPDS". The TMPPDS table has all the information on individual projects. This isnt a problem. (2) Print the individual report records to Micorosft Word using the current "Name" field on the report as the Word file name. I am using Access 2002.

This is what I have so far. If I debug.Print rst!Name in the immediate window it shows the names of my projects from the TmpPDS table. When I insert the word line the full path is correct in the immediate window but the word line ---objWord.Application.ActiveDocument.SaveAs (stFullFilePath) gives me an error 424 Object required. Any suggestions on this one. I have the code attached to a report and I execute the code on the report using the on open event.


Public Sub WordDocuments()
Dim stFullFilePath As String
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "TmpPDS", cnn

Do Until rst.EOF

FullFilePath = "C:\temp\Documents\" & rst!Name & ".rtf"

objWord.Application.ActiveDocument.SaveAs (stFullFilePath)

rst.MoveNext

Loop

End Sub

 

[RockyStone]

Update: Chris This code works but with one problem. If the underlying table has 5 records I get 5 complete report outputs to rtf. with the all 5 reports in each rtf. I can't seem to get the loop to print each record individually. Any assistance would be appreciated.. TIA Heres the working code:

Public Sub WordDocuments()
Dim stFullFilePath As String
Dim strDocName As String
strDocName = "rptPDS"
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "TmpPDS", cnn


Do Until rst.EOF

DoCmd.OpenReport strDocName, acPreview
FullfilePath = "C:\temp\Documents\" & rst!Name & ".rtf"

DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, (FullfilePath)

rst.MoveNext
Loop


End Sub

 

[ChrisCalvert]

OK, got it.
This turned out to be trickier than I thought, and I am not sure this is the absolute best way, but it does work. (sorry about last week, Vacation.)

Things to do for this to work.
1) Put a text box txtRptUse on the form that will generate this. Hope the requirement of a form does not ruin anything.
2) Put the following as your filter in the report and turn filters on: [DepartmentID] = [Forms]![frmReports]![txtrptUse]



--------------------------------------------
Dim stDocName As String
Dim FullFilePath As String

stDocName = "rptDepts"
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "tblDepartments", cnn
stDocName = "rptDepts"

Do Until rst.EOF
FullFilePath = "C:TextFiles\" & rst!DepartmentID & ".rtf"
Me.txtrptUse = rst!DepartmentID
DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, FullFilePath
rst.MoveNext
Loop
--------------------------------------------------------

Hope that helps. I tried just manipulating the filter on the report with Report_rptDept.Filter (that is, the full name) but it complained that this had to be an object.

-Chris