Path to MY DOCUMENTS 1

[tonyflavell]

I want to open an Excel sheet, from Access, which is in the MY DOCUMENTS directory. How do I specify this for the current profile? Eg:

Dim objXL as Excel.Application
Dim BaseDir as String

BaseDir = "C:\My Documents\UsagesReport.xls")
Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open BaseDir

This does not find the MY DOCUMENTS directory! HELP! I'm sure there is a simple way that I am not aware of!

 

[earthandfire]

C:\Documents and Settings\User\My Documents is the default location.

I think there is a RegistryKey but I'm not sure which one.

 

[tonyflavell]

Thanks earthandfire. I realise that the path must be

C:\Documents and Settings\.....\My Documents

but the user profile could be anybody on any of 50 client workstations on the network. I don't want to hardcode the "user" profile.

 

[DJN]

Try using the full path name.

Call Shell("C:\program files\Microsoft Office\Office11\excel.exe PathName:\XL\XLSFileName.xls", 1)

 

[oharab]

Try this code:

' Get path of Special folders
Type ShortItemId
     cb As Long
     abID As Byte
End Type

Type ITEMIDLIST
     mkid As ShortItemId
End Type

Const CSIDL_PROGRAMS = 2                  ' Program Groups Folder
Const CSIDL_PERSONAL = 5                  ' Personal Documents Folder
 Const CSIDL_FAVORITES = 6                 ' Favorites Folder
 Const CSIDL_STARTUP = 7                   ' Startup Group Folder
 Const CSIDL_RECENT = 8                    ' Recently Used Documents Folder
 Const CSIDL_SENDTO = 9                    ' Send To Folder
 Const CSIDL_STARTMENU = 11                ' Start Menu Folder
 Const CSIDL_DESKTOPDIRECTORY = 16         ' Desktop Folder
 Const CSIDL_NETHOOD = 19                  ' Network Neighborhood Folder
 Const CSIDL_TEMPLATES = 21                ' Document Templates Folder
 Const CSIDL_COMMON_STARTMENU = 22         ' Common Start Menu Folder
 Const CSIDL_COMMON_PROGRAMS = 23          ' Common Program Groups Folder
 Const CSIDL_COMMON_STARTUP = 24           ' Common Startup Group Folder
 Const CSIDL_COMMON_DESKTOPDIRECTORY = 25  ' Common Desktop Folder
 Const CSIDL_APPDATA = 26                  ' Application Data Folder
 Const CSIDL_PRINTHOOD = 27                ' Printers Folder
 Const CSIDL_COMMON_FAVORITES = 31         ' Common Favorites Folder
 Const CSIDL_INTERNET_CACHE = 32           ' Temp. Internet Files Folder
 Const CSIDL_COOKIES = 33                  ' Cookies Folder
 Const CSIDL_HISTORY = 34                  ' History Folder

 Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    (ByVal pidl As Long, _
    ByVal pszPath As String) As Long

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


Sub ShowFolder()
    Dim lngID As Long
    Dim IDL As ITEMIDLIST

    Dim strPath As String
    Dim strShortCut As String

    ' Fill the idl structure with the specified folder item.
    lngID = SHGetSpecialFolderLocation(0, [COLOR=red]CSIDL_PERSONAL[/color], IDL)

    ' Get the path from the idl list, and return
    ' the folder with a slash at the end.
    If lngID = 0 Then
        strPath = Space$(260)
        lngID = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal strPath)

        If lngID Then
            strPath = Left$(strPath, InStr(strPath, Chr$(0)) - 1) & "\"
        End If

        If strPath <> "" Then
            MsgBox "My documents = " & strPath
        Else
            MsgBox "Unable to find"
        End If
    End If

End Sub

Change the highlighted text to get a different folder.

hth

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F

 

[earthandfire]

I'm not certain that this will work 100% of the time, but it is definitely worth investigating the USERPROFILE environment variable.

Environ("USERPROFILE") & "\My Documents"

works on each of my machines with different logons

 

[tonyflavell]

Brilliant!!! earthandfire. Good bit of parallel thought. A star is born and awarded

 

[earthandfire]

Thanks. Glad to have helped.