export report

[slimjen]

All, Using 2010. I am working on a database that was created in 2003. I created a form with a button to export a report to the users desktop in a folder name "Reports". Both the user and myself have access 2010 running on a win 7 machine. When I click the button; the code runs as expected and exports the report in the folder. When the user clicks the button, they get an error :"...can't save the output data to the file you've selected." Here's the code:

Private Sub Export_QA_Report_Click()
On Error GoTo Err_Export_QA_Report_Click
Dim reportname As String
Dim theFilePath As String
reportname = "rptQAReport"
theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Reports\"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"

DoCmd.OutputTo acOutputReport, reportname, acFormatXLS, theFilePath, True
MsgBox "Look on your desktop for the report."
    
Exit_Export_QA_Report_Click:
    Exit Sub
Err_Export_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_Export_QA_Report_Click
    
End Sub

Why can't the users run it and I can?
Thanks

[JoeM]

Per this line:

theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Reports\"

it looks like it is looking for a specific file path to place the file in. Make sure that the user having problems has such a file path on their computer (especially the Desktop\Reports subdirectory found after their username).

[slimjen]

Just found out that users do not have access to their Doc and Settings. How can I code it so it appears on their destop w/ or w/o a folder i.e. "reports". They do have access to "My Computer". Thanks

[JoeM]

I would recommend picking some common shared drive you all have access to.
Or, another thing I have done before is to create a one-record table that stores these values, and they create a form of it so that users can change the settings themselves to a directory that they have access to.

[slimjen]

I tried the shared drive approach but still no joy. I do remember something similar to giving the users the option where to save but don't remember the code. ???

[JoeM]

Its really quite simple. Just create a table that hold the export directory location, and then create a Form based on that table where the users can enter it in directory.
I usually create the one record first, then change the Form property to not allow any additions or deletions and getting rid of all navigation buttons, to help ensure there will always be only exactly one record in the table.

I then place a "Run" command button on Form, which runs Export, incorporating the value they entered in to the Export Directory text box. So in your VBA code, this line:

theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Reports\"

might be replaced with something that looks like this:

theFilePath = Me.txtExportDirectory

where txtExportDirectory is the name of the Text Box on the Form.

[slimjen]

Oh. This is not what I remembered. I was thinking when outputing the report; it pop ups the save dialog to prompt the users to save the report in excel format whereever they want. Forgot the code though to complete this

[JoeM]

You could do that too, though it may be a bit harder to program. You can do a Google Search and find that code on the internet. Here is one I used years ago" http://answers.microsoft.com/en-us/o...f-50cc0f3d3d46