Results 1 to 8 of 8
  1. #1
    RMittelman is offline Advanced
    Windows XP Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Get "My Documents" in Access 2007 macro

    Hi,



    This should be so simple, but can't seem to find it:

    I want to save an Access 2007 query to a spreadsheet (from a macro).
    The OutputTo action works fine if I leave the destination blank. It brings up the File/Save dialog, and defaults the spreadsheet name to the query name, and defaults the folder to My Documents.

    If I give the OutputTo a specific folder and file name, that works fine also.

    I can't figure out how to save it in a sub-folder of My Documents.
    If I'm John Doe, I can save it to "C:\Users\John Doe\Documents\SubFolder" fine in Windows 7.

    What If I want this to work for any user? How do I shorthand the "My Documents" folder?

    This is needed in a macro, not in VBA.

    Thanks...

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Create the folder like:

    Code:
    Dim strPath As String
     
    strPath = "C:\Users\" & VBA.Environ("username") & "\Documents\SubFolder\"

  3. #3
    RMittelman is offline Advanced
    Windows XP Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Not Quite...

    Thanks, Bob, but this wasn't quite what I need.

    This is VBA code. I need a way to refer to the current user's folder in an Access 2007 macro.

    Even if I could use VBA, this wouldn't work for Windows XP. I'm sure there should be a function or environment variable which refers to the user's folder, and is OS-agnostic.

    Once I know that, I just need to know how to access it in a macro instead of VBA.

    Thanks...

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You will need to use a Windows API if you want it OS-agnostic.

    Copy this API code to a new standard module:
    http://access.mvps.org/access/api/api0054.htm

    And then you SHOULD be able to call it using:

    fGetSpecialFolderLocation(CSIDL_Personal)

    I've not tested with anything greater than Windows XP though, so you'll have to try it out.

  5. #5
    RMittelman is offline Advanced
    Windows XP Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    So, if I understand you correctly, what I could accomplish easily in VBA by using environ("userprofile")

    (which returns "C:\Users\name" in Win7 and "C:\Documents and Settings\name" in Win XP)

    will not work at all in a macro?

    At the very least, I know I can define a VBA function which I can call in the macro, which WILL work. I was hoping it would work straight from the macro without having to run code.

    Thanks...

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You can try using the Environ function but that is not foolproof. But you would probably have to use a TempVar in the macro to build a string with the whole path.

  7. #7
    RMittelman is offline Advanced
    Windows XP Access 2007
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Yes, that's what I was planning. So now I guess I need a VBA function:
    Code:
    Public Function GetEnviron(EnvVariable As String) As String
        GetEnviron = Environ(EnvVariable)
    End Function
    Then I need to have a line in my macro:
    SetTempVar
    Variable: MyFolder
    Expression: GetEnviron("userprofile") & "\My Documents\SubFolder"

    Then the TempVar MyFolder contains:
    "C:\Users\name\My Documents\SubFolder" in Win7, and
    "C:\Documents and Settings\name\My Documents\SubFolder" in XP.

    This works in both OS's. I was just hoping to not need the VBA function.

    Please tell me why you think the Environ function is not dependable.

    Thanks...

  8. #8
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by RMittelman View Post

    Please tell me why you think the Environ function is not dependable.
    1. It can be modified by a user or by other programs.

    2. It sometimes isn't recognized under some circumstances (and sometimes it will work if prefaced by VBA like VBA.Environ("userprofile") but even then it may not. I'm not sure why or exactly what the circumstances are but I've seen that over the course of time with the different forum posts.

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

Similar Threads

  1. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  2. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  3. Set "My Documents" Path
    By jhrBanker in forum Programming
    Replies: 6
    Last Post: 05-25-2010, 07:42 PM
  4. docmd.SendKeys "{F9}" in 2007
    By RickM in forum Access
    Replies: 4
    Last Post: 09-10-2009, 09:13 AM
  5. Replies: 0
    Last Post: 09-25-2008, 12:19 PM

Tags for this Thread

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