Environ Functions - Save an export to any desktop

[cbrsix]

Hi,



I have the below code that is used to generate a report, save it as a PDF to a specific users desktop, and to print it out. Now more than one person is responsible for this activity. The problem I am running into is that the OutputTo code is specific to only the original users desktop. I want to allow any user to perform this duty from their own computer. I have potentially found a solution by utilizing Environmental functions. However, I have no experience with them and am not exactly sure how to properly integrate it into my current code.

Private Sub cmdPrint_Click()

Dim dbs As Object
Dim rst As Object
Dim strSql As String
Dim strWhere As String

strSql = "Account36MonthList" 'Select the Query where you want your information to be drawn from

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql)

    rst.MoveFirst
    While Not rst.EOF
        strWhere = " [PTSAccountNumber] = """ & rst![PTSAccountNumber] & """"
'        DoCmd.OpenReport "36MonthCoverLetter", acViewNormal, , strWhere, , "False" 'Opens the report based on the strWhere and prints the report
        DoCmd.OpenReport "36MonthCoverLetter", acViewPreview, , strWhere, , "False" 'Opens the report based on the strWhere to be output to PDF
        DoCmd.OutputTo acOutputReport, "36MonthCoverLetter", acFormatPDF, "C:\Users\REDACTED\Desktop\Cover Letter " & rst!ClientName & " " & Right(rst![Account Number], 4) & ".pdf" 'Saves the individual report at a PDF
        DoCmd.PrintOut
        DoCmd.Close acReport, "36MonthCoverLetter"
        rst.MoveNext
    Wend

Set rst = Nothing

Me.cmdPrint.Enabled = False 'makes the button unclickable so a task does not get duplicated
End Sub

Here is a link to another forum where I found the potential solution.http://https://www.access-programmer...esktop.279464/

[Welshgasman]

Link does not work?
Link is actually https://www.access-programmers.co.uk...esktop.279464/

Try as an example from that link.

Dim UserProfilePath as String
UserProfilePath = Environ("Userprofile")
Dim abre
abre = Shell("explorer.exe "  & UserProfilePath & "\Documents\Backups", vbNormalFocus)

Use Debug.Print or the immediate window to check your values.

Or then you could just Google the function?

[jojowhite]

change this line:

        DoCmd.OutputTo acOutputReport, "36MonthCoverLetter", acFormatPDF, "C:\Users\REDACTED\Desktop\Cover Letter " & rst!ClientName & " " & Right(rst![Account Number], 4) & ".pdf" 'Saves the individual report at a PDF

To:

        DoCmd.OutputTo acOutputReport, "36MonthCoverLetter", acFormatPDF, Environ$("Userprofile") & "\Desktop\Cover Letter " & rst!ClientName & " " & Right(rst![Account Number], 4) & ".pdf" 'Saves the individual report at a PDF

[June7]

Why save business communications in private user folders? Don't want saved in a centralized location?

Could save to same folder where database frontend is located:

DoCmd.OutputTo acOutputReport, "36MonthCoverLetter", acFormatPDF, CurrentProject.Path & "\CoverLetter\" & rst!ClientName & " " & Right(rst![Account Number], 4) & ".pdf"

Code should probably check that CoverLetter folder exists and if it doesn't, create it.