Export report

[slimjen]

Hi all, using access 2016. I have a button on a form to export a query on to the users desktop in a folder. This was working up until a few days ago.

Private Sub Command9_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "qryPrintrecord"
theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
 
DoCmd.acOutputQuery, reportname, acfortmatXLS, theFilePath, True
MsgBox "Look on your desktop for the report."

The error msg MSaccess cant save the output data to the file you've selected.



I tried docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, reportname, the Filepath true...
This worked but when the user went to open the dile error msg the file format n extension of qry dont match. The file may be corrupted...pls help..thanks

[pbaldy]

Is that the actual code? I would have assumed it was

DoCmd.OutputTo...

Should there be a \ after Output in the file path?

[slimjen]

sorry; left that off but was there....Still not working....

[pbaldy]

What is the actual code?

[slimjen]

Private Sub Command9_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "qryPrintrecord"
theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output\"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
 
DoCmd.acOutputQuery, reportname, acfortmatXLS, theFilePath, True
MsgBox "Look on your desktop for the report."

I get the error msg: MSaccess cant save the output data to the file you've selected...The file is .xls. Do you think that has anything to do with it?

[pbaldy]

I can't see that being the actual code. I'm not on a computer right now but I can't imagine either of the items in red being valid:

DoCmd.acOutputQuery, reportname, acfortmatXLS

[ssanfu]

PMFJI,

The syntax is

expression.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, OutputQuality)

it looks like you want to output a query, so the DoCmd statement would look like

DoCmd.OutputTo acOutputQuery, reportname, acFormatXLS, theFilePath, True

To output a report, you could also try

DoCmd.OutputTo acOutputReport, reportname, acFormatXLS, theFilePath, True

Private Sub Command9_Click()
    Dim reportname As String
    Dim theFilePath As String
    reportname = "qryPrintrecord"
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output\"
    theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
    
    Debug.Print theFilePath

    DoCmd.OutputTo acOutputQuery, reportname, acFormatXLS, theFilePath, True

    MsgBox "Look on your desktop for the report."
End Sub

[slimjen]

Thanks all for responding...I am trying to export a query ...sorry the reportname is throwing u off. Maybe I'm getting the error because of getusername??? Environ(username)