Saving a file using Output To command

[Jackie]

I would like to automatically save a report to a file. I would like the file name to include the name of a value on the report. I use the OutputTo command to export the report to an .rtf file. This code is executed on the "OnPage" Event. I set the Below is the code that I use, which gives the "Object Required" error on the following line of code:

Set strReportName = Reports!PM_Proj_Num!Text95

The report field referenced exists in the report being run.

Any ideas would be greatly appreciated.


Private Sub Report_Page()

Dim strReportName As String

'Establish the report file name
Set strReportName = Reports!PM_Proj_Num!Text95

'Save the report to a file
DoCmd.OutputTo acReport, "PM_Proj_Num", "RichTextFormat(*.rtf)", "", False, """" & strstrReportName & """"

'Close the report to contine processing the remaining Program Managers' reports
DoCmd.Close acReport, "PM_Proj_Num"
End Sub

 

[VBslammer]

You only need to use the 'Set' statement for object variables. If you had a variable declared as:

Dim rpt As Report

then you would use:

Set Report = Reports("myReportName")

However, this only works for open objects, so you'd need to open the report before you set a reference to it:

DoCmd.OpenReport strReportName, acViewDesign

In your case, you're using a string variable so just remove the set statement and the collection reference:

strReportName = PM_Proj_Num!Text95

then run your output:

DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF, "C:\MyExportedReport.rtf"

VBSlammer

 

[VBslammer]

Also, in your OutputTo statement, are you opening a template file? I noticed you did not provide a file name to save the report to (4th argument). The 6th argument is for a template file name.

I think you intended to use strReportName as the saved file name. Another thing you should remember is that when you are referring to the running report you can use the 'Me' keyword:

DoCmd.OutputTo acReport, Me.Name, acFormatRTF, strReportName

I've never seen anyone try to do something like this in the Page() event of a report so I don't know if this will work for you.

VBSlammer

 

[Jackie]

Thank you for responding.

I have made some progrgess. My current question is how to direct the report to a specific directory, without specifying a harddrive. I believe it is a syntax error, any ideas?

This is the current code which gives the error "...can't save the output to the file selected". This error occurs on the OutputTo statement.


Private Sub Report_Page()

'Identify a variable to hold the report file name
Dim strReportName As String

'Establish the report file name
strReportName = "\\PM_Reports\" & Right(Reports!PM_Proj_Num!Text95, 5) & "." & "rtf"

'Save the report to a file
DoCmd.OutputTo acReport, "PM_Proj_Num", "RichTextFormat(*.rtf)", strReportName

'Close the report to contine processing the remaining Program Managers' reports
DoCmd.Close acReport, "PM_Proj_Num"

End Sub


Here are a few other notes:

I removed the Set from the variable statement.

Logic errors occurred when I used the OnOpen or OnClose commands, that is why I used Page.

I also got an error when I used Me!Reports.

 

[VBslammer]

It ran on my box like this:

Private Sub Report_Page()

    Dim strReportName As String
    
    strReportName = "C:\" & Right(Me.TrailerNbr, 5) & "." & "rtf"
    
    DoCmd.OutputTo acReport, Me.Name, "RichTextFormat(*.rtf)", strReportName
    
    DoCmd.Close acReport, Me.Name

End Sub

You must be trying to save the file to a drive that doesn't exist or maybe the filename is null or something. Step through the code with your debugger to see if all your variable values make sense.
VBSlammer