output to excel (1 Viewer)

[slimjen1]

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

 

[JHB]

Could it be because the path doesn't exist on the other users computer (or users are not allowed to save documents in the drive/path)?

Try to export it only to "C:\".

 

[slimjen1]

Thank you for your response. You are correct; users do not have access to the path. I need it to go to their desktop or they have access to My Computer. Any suggestions? Thanks

 

[pr2-eugin]

Is the path to Desktop not

[COLOR=Red][B]C:\Users\[COLOR=Green]<USER NAME>[/COLOR][COLOR=Black]\Desktop\Reports\[/COLOR][/B][/COLOR]..

Why have you constructed the path as..

 [COLOR=Red][B]C:\Documents and Settings\[COLOR=Green]<USER NAME>[/COLOR][COLOR=Black]\Desktop\Reports\

[/COLOR][/B][/COLOR]

 

[slimjen1]

Thank you for your reply. Some of the users have Win xp. But I will try this path.
Thanks

 

[jkl0]

You can just use the environment variable for windows 7,

%USERPROFILE% = C:\Users\{username}

 

[slimjen1]

I still get the error. Maybe because the users do not have access to docs and settings or their users folder. I don't know if that's the problem and/or do not know how to get around it.
thanks

 

[pr2-eugin]

Thank you for your reply. Some of the users have Win xp.

Really?? Your First post said..

Both the user and myself have access 2010 running on a win 7 machine.

 

[slimjen1]

You can just use the environment variable for windows 7,

%USERPROFILE% = C:\Users\{username}

? Do I use this as is? "%USERPROFILE% = C:\Users\{username}"

Umm. Still doesn't work. What am I doing wrong?

 

[jkl0]

I don't have a Windows 7 machine to test this on, but try

Dim strUser as String
strUser = Environ("USERPROFILE")
theFilePath = strUser & "\Desktop\Reports\"

 

[slimjen1]

Doesn't work. Still getting the same error. Maybe I should export the query instead but this seems that it can work with output report. Don't know what to do. It's got to be something with the path but I've tried different ways to export it.

 

[jkl0]

See if the info in the link below will help.

http://www.access-programmers.co.uk/forums/showthread.php?t=166247