Button to Export

[cuddles]

I have a button on a report and when I click that button I want the option to export to pdf or excel. I have tried multiple coding options and ended up with the following:

Private Sub cmdExport_Click()
Dim tag As String

If MsgBox("Do you want to export to Excel?" , _
vbQuestion + vbYesNo) = vbYes Then
DoCmd.OutputTo acOutputReport, "rptCA", acFormatXLSX, "C:\"
Reports("rptCA").page.Orientation = acPRORLandscape
DoCmd.OpenReport "rptCA", acViewNormal
End If

If vbQuestion + vbYesNo = vbNo Then
DoCmd.OutputTo acOutputReport, "rptCA", acFormatPDF
Reports("rptCA").page.Orientation = acPRORLandscape
DoCmd.OpenReport "rptCA", acViewNormal
End If
End Sub

The problem is no matter if I click Yes or No, it always goes to PDF.

[alansidman]

Try changing the acFormatXLSX to acFormatXLS

I believe that acFormatXLSX is not a valid format. Who knows why the MS Engineers did this?

[cuddles]

I changed it and the pop-up works but yet when I click yes it automatically takes me to pdf. It does not give me an option other than pdf or all files. If I change to all files and then manually put .xls replacing the .pdf, it will save as Excel, but then when I view the Excel it is in XML language. Is there not a way to say if you click on yes, it will save as Excel and if no autosave PDF?

[ranman256]

The answer to the msgbox is only vbYes or vbNo
Dont add vbQuestion. Store the result in varible like ans = msgbox(...

[June7]

Try If Then Else since there are only two possibilities. If you want to allow 3 (vbYesNoCancel) then definitely need a variable to store the response.

If MsgBox("Do you want to export to Excel?" , _
vbQuestion + vbYesNo) = vbYes Then
DoCmd.OutputTo acOutputReport, "rptCA", acFormatXLSX, "C:\"
Reports("rptCA").page.Orientation = acPRORLandscape
DoCmd.OpenReport "rptCA", acViewNormal
Else
DoCmd.OutputTo acOutputReport, "rptCA", acFormatPDF
Reports("rptCA").page.Orientation = acPRORLandscape
DoCmd.OpenReport "rptCA", acViewNormal
End If

[cuddles]

Hum, I get the following error: compile error: invalid qualifier on the page. I changed it to printer and it gives me error 2302. MS cant save the output data to the file you've selected. I tried acFormatXLS and also doing Z:\ instead of C and same error

[June7]

Did you run Debug>Compile?

Which line does the debugger actually stop on?

The OutputTo commands are incomplete. Not giving full path/filename to save to. Both formats work for me.

However, I am not sure if can set the report orientation unless it is open in design view. Suggest you comment the two lines for printing and make sure the exports work first.

[cuddles]

This is what I am doing

Private Sub BtnExport_Click()
If MsgBox("Do you want to export to Excel?" , _
 vbQuestion + vbYesNo) = vbYes Then
 DoCmd.OutputTo acOutputReport, "rptCA", acFormatXLSX, "C:\"
 'Reports("rptCA").page.Orientation = acPRORLandscape
 'DoCmd.OpenReport "rptCA", acViewNormal
 Else
 DoCmd.OutputTo acOutputReport, "rptCA", acFormatPDF
 'Reports("rptCA").page.Orientation = acPRORLandscape
 'DoCmd.OpenReport "rptCA", acViewNormal
 End If 
End Sub

It bombs on DoCmd.OutPutTo acOutputReport, "rptCA", acFormatXLS, "C:\"

Does not like XLSX either

[June7]

You don't have full path/filename.

DoCmd.OutPutTo acOutputReport, "rptCA", acFormatXLS, "C:\filename.xls"

DoCmd.OutputTo acOutputReport, "rptCA", acFormatPDF, "C:\filename.pdf"

[cuddles]

Ok, I commented out the items that talk about landscape and all and I need to put , , after the acFormatXLS. So it is acFormatXLS , , "C:\"..........However. it still does not like the .page. I am playing around with different ones in there. If I put it on .printer it will save the file but also print and that is not what I want. I will keep searching. Have presentation at 2pm and wanted to be able to show them the change in the printing button but don't think that will happen. They wanted an export I mean. Just going nutso with this 14 hour days and this rush build that I am clueless on. I am sure I will figure it out and will let you know.

[cuddles]

It's working but in Excel is looks awful...........I wished they did not want to export a report to Excel. Maybe I can figure out how to output it in report view or something so it looks like it does on the form screen.

[June7]

I do not think Report View can be exported.

[ItsMe]

...........I wished they did not want to export a report to Excel...

One comment I hear often when I show a user a form (GUI) is, "Wow, can you put that in Excel?

One thing I will do is use the query a report is based on and export the query. If there are too many fields and Grouping in the report. I will build multiple query objects, one query for each grouping and move to automation of Excel, Exporting each Query to a range in Excel. Some people just need data in Excel to grasp the numbers.