determine users profile path for export path in MS Access

Posted on 2009-12-31
Last Modified: 2013-12-05
I'm exporting reports and queries within MS Access ADP application.  I've been using the users 'C' drive as the location of there "My Documtents" folder, but this isn't the case when the user works remotely.
How do I determine the path of their "My Documents" folder so I can export the file there?

Below is some of the code showing where I hard coded the path and an attempt at using
%Userprofile% as a wild card which didn't work.  myGetUserName is a function I use to get
the login name of the user.

Help is appreciated.

On Error GoTo viewReport_Error
    Dim myReport As String
    Dim temp As String
    temp = %Userprofile% & myGetUserName & "\My Documents\"
    myReport = Me!lbReportName.Column(2)
    myFilePath = "C:\Documents and Settings\" & myGetUserName & "\My Documents\"
Question by:jvantassel1
    LVL 39

    Expert Comment

    The format you tried %Userprofile% is for batch files. For VB, Try:
     temp = Environ("Userprofile") & myGetUserName & "\My Documents\"
    LVL 39

    Expert Comment

    For user name use;
    LVL 3

    Accepted Solution

    thenelson is right. however, i believe that
    temp = Environ("Userprofile") & "\My Documents\"
    should be enough as userprofile will return also the login name.

    LVL 1

    Author Closing Comment

    This worked perfectly.  I've been coding in Access for years and didn't know this.  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now