Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output to My Documents (2003)

    Hello Again

    Sorry to bug you with 2 questions in 1 day, but I have 1 more question I'd like to ask.

    I would programmatically like to output the results of a query to each user's My Documents Folder. I know that in Windows Active Directory and via DOS Prompts, I can use %username% to get Windows to select the Current User, but I'm wondering if I can use this syntax to get Access to ouput there too?

    I tried "Cocuments and Settings%username%My DocumentsData.xls", but this returned an error. Before I take it that Access doesn't work the same way as Windows & AD, can someone advise if there is a way to do this, i.e., am I using the wrong syntax?

    Thanks for your help.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Output to My Documents (2003)

    The most reliable method for this would be to use Windows API functions. See recent reply in another thread:

    <!post=Re: Finding User's My Documents (A2000),347838>Re: Finding User's My Documents (A2000)<!/post>

    HTH

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to My Documents (2003)

    So, I would put this above my own code:
    Public Const CSIDL_PERSONAL = &H5 'My Documents
    Public Declare Function SHGetPathFromIDList Lib "shell32" _
    Alias "SHGetPathFromIDListA" _
    (ByVal pidl As Long, ByVal pszPath As String) As Long

    Public Declare Function SHGetSpecialFolderLocation Lib "shell32" _
    (ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As Long) As Long

    Public Declare Sub CoTaskMemFree Lib "ole32" (ByVal pv As Long)

    Public Function GetSpecialFolderLocation(CSIDL As Long) As String

    Dim strPath As String
    Dim pidl As Long

    If SHGetSpecialFolderLocation(Application.hWndAccessA pp, CSIDL, pidl) = 0 Then
    strPath = Space$(MAX_PATH)

    If SHGetPathFromIDList(ByVal pidl, ByVal strPath) Then
    GetSpecialFolderLocation = Left$(strPath, InStr(strPath, Chr$(0)) - 1)
    End If

    CoTaskMemFree pidl
    End If

    ... and then on the final line, I would change it to read:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_wordmerge", "strPathdata.xls", True
    End Function
    End Sub

    Sorry, but I've never used APIs before.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output to My Documents (2003)

    Almost, but not quite. Copy the code from the post MarkD referred to "as is". Don't put the DoCmd.TransferSpreadsheet line inside that code.

    In the code to export to Excel (from a command button or something like that), use this:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_wordmerge", GetSpecialFolderLocation(CSIDL_PERSONAL) & "data.xls", True

    The path to the user's My Documents is concatenated with the name of the workbook.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to My Documents (2003)

    OK, I'll try that.

    Thanks you Hans and Mark.

    I'll post back and let you know how it went! [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to My Documents (2003)

    Hi

    Just wanted to report back and say a big thank you for your help with this.

    You have no idea how much easier that's going to make my life!

    You're both stars.

    Thanks again! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •