Results 1 to 4 of 4
  1. #1
    adammitchell is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2017
    Posts
    5

    Exporting to a location on a local machine regardless of user, Access 2007.

    Hi all,



    I am trying to export the results of a query to the user's desktop, regardless of the username. Currently I have the following, which works fine, but only if the user's name is "User".

    Code:
    Private Sub Command1_Click()
    On Error Resume Next
    DoCmd.TransferText acExportDelim, "DistanceOutput", "q_Output_For_Distance_Program", "C:\Users\user\Desktop\DistanceOutput.txt", True, ""
    Where "DistanceOutput" is my export specification and "q_Output_For..." is my query. If the user's username is "Bob" or "Frank", this obviously won't work.

    Many thanks!

    Adam

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The function Environ("UserProfile") will return the file location for each user

    Try this:

    Code:
    Private Sub Command1_Click()
    On Error Resume Next
    
    Dim strText As String
    'build the string for the output file on the user's desktop
    strText = Environ("UserProfile") & "\Desktop\DistanceOutput.txt"
    
    'Debug.Print strText
    
    DoCmd.TransferText acExportDelim, "DistanceOutput", "q_Output_For_Distance_Program", strText, True, ""
    But, as the final argument is blank it can be omitted. This is OK:

    Code:
    DoCmd.TransferText acExportDelim, "DistanceOutput", "q_Output_For_Distance_Program", strText, True
    HTH

    Colin

  3. #3
    adammitchell is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2017
    Posts
    5
    Perfect - thanks so much Colin!

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    In case you aren't aware of what the Environ function can do, run this from a module.

    Code:
    Sub ShowEnviron()
    
    'lists all environment variables
        Dim strg As String
        Dim x As Long
    
    
        strg = "Environ Values 1  to 40" & vbCrLf & _
        "=======================" & vbCrLf
        For x = 1 To 40
            strg = strg & x & "  " & Environ(x) & vbCrLf
        Next x
        
        Debug.Print strg
    
    
    End Sub
    This will list all Environ values for that computer to the Immediate window in the VBE

    To use any of these type e.g. Environ("UserName")

    HTH

    Colin

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-15-2014, 12:41 PM
  2. Register Access on my local machine
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 05-07-2014, 06:42 PM
  3. Replies: 1
    Last Post: 02-21-2014, 09:16 AM
  4. Access 2007 and 2010 on same machine
    By SmartestIdiot in forum Access
    Replies: 6
    Last Post: 01-26-2014, 01:36 PM
  5. Access 2007 - 64 bit Machine
    By greggue in forum Access
    Replies: 1
    Last Post: 09-28-2010, 12:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums