Thread: View Report (With Excel) - vba code?

I have a database that currently when you click the button on the form, it opens the report "rpt_Test".

I have to then manually view it with excel and save it.

I'm looking for a code that will do the following:

1) Click the button on form = msgbox ("View report or export?") vbyesno
2) Yes = View report
2a) No = Export into S:\Testing\TestReport.xls
2b) msgbox ("Successfully exported!")
3) exit report view and just show form

I tried looking through the macro commands and couldn't find anything, so i'm ok using VBA instead

Thanks for all the help!

Hi,

Is "rpt_test" a workbook, worksheet or range?

Rgds

Hi adgjqetuo,

Here are the VBA DoCmd codes you could use if you went with two buttons:

To view the report:

Code:

DoCmd.OpenReport "rpt_Test", acViewNormal, acPreview

To export the report to a file:

Code:

DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rpt_Test", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="S:\Testing\TestReport.xls", _
    AutoStart:=True

Cheers,

Originally Posted by ConneXionLost

Hi adgjqetuo,

Here are the VBA DoCmd codes you could use if you went with two buttons:

To view the report:

Code:

DoCmd.OpenReport "rpt_Test", acViewNormal, acPreview

To export the report to a file:

Code:

DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="rpt_Test", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="S:\Testing\TestReport.xls", _
    AutoStart:=True

Cheers,

Thanks for the response! The export code works perfectly only it opens the excel file too. I want it to export "behind the scenes" and not actually open - just save. I tried turning off notifications but that didn't work. Any ideas?

Would turning auto start to "false" do the trick? I didn't think to try that earlier.

Also, what's the access vb code for yes no message box? Is it the same as excel?

IE:
if msgbox ("test", vb yesno) = vbyes then
<code>
else
<code>
end if

Hi adgjqetuo,

Try changing:

AutoStart:=True

to:

AutoStart:=False

Cheers,

Hi adgjqetuo,

You can use a MsgBox using the syntax described here:

MsgBox

But you may prefer to build your own form for this purpose.

Cheers,