Finding My Documents

[WhoCanDo]

Hi,

I want to write and read a text file inside My Documents but XP has My Documents in one path and Win'7 has it in another.

How do I find the My Documents path so I can concatenate it with my filename in VBA?

Thanks

 

[Jon von der Heyden]

Paste this into a module:

Public Declare Function SHGetSpecialFolderLocation _
    Lib "shell32" (ByVal hWnd As Long, _
    ByVal nFolder As Long, ppidl As Long) As Long

Public Declare Function SHGetPathFromIDList _
    Lib "shell32" Alias "SHGetPathFromIDListA" _
    (ByVal Pidl As Long, ByVal pszPath As String) As Long

Public Declare Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As Long)
    
Public Const CSIDL_PERSONAL = &H5
Public Const CSIDL_DESKTOPDIRECTORY = &H10
Public Const MAX_PATH = 260
Public Const NOERROR = 0

Public Function SpecFolder(ByVal lngFolder As Long) As String
    Dim lngPidlFound As Long
    Dim lngFolderFound As Long
    Dim lngPidl As Long
    Dim strPath As String
    
    strPath = Space(MAX_PATH)
    lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl)
    If lngPidlFound = NOERROR Then
        lngFolderFound = SHGetPathFromIDList(lngPidl, strPath)
        If lngFolderFound Then
            SpecFolder = Left$(strPath, _
                InStr(1, strPath, vbNullChar) - 1)
        End If
    End If
    CoTaskMemFree lngPidl
End Function

Then, to get My Documents, you could use:

Dim strMyDocuments as String
strMyDocuments = SpecFolder(CSIDL_PERSONAL)

Edit: You can add other special folders too, or you can simplify and use only &H5 CSIDL_PERSONAL.

 

[WhoCanDo]

I was thinking of something more simpler like Application.DefaultFilePath

Application.DefaultFilePath is OK if the user does not change the defaults.

Any other suggestions please?

 

[Jon von der Heyden]

Another way:

Public Function MyDocs()
    Dim objWSH As Object
    
    Set objWSH = CreateObject("WScript.Shell")
    MyDocs = objWSH.SpecialFolders.Item("mydocuments")
End Function

 

[WhoCanDo]

That's simple Jon but how does it work?

You create a .shell file and then find it?

Does this depend on the Excel default folder locations in Options/General?

Thanks

 

[Jon von der Heyden]

Hi

This isn't looking to the excel Application. WScript (Windows Scripting) provides access to the native Windows shell. The SpecialFolders is the collection of 'special folders' on the users machine.

There is no creation of any file, in this instance 'windows shell' refers to the windows interface.

 

[Ruddles]

Thanks Jon for the useful info in this thread - I will be using it in a current project.

For anyone interested in knowing this, a complete set of values for the CSIDL constants can be found at
http://www.jasinskionline.com/windowsapi/ref/other/csidls.html.

 

[Jon von der Heyden]

Or the MSDN page.

 

[Ruddles]

I looked there but the values aren't given. The names themselves didn't work in my test program until they were declared explicitly.

Am I being a complete duffer?

 

[Jon von der Heyden]

Nope, it's me

I pasted the wrong link. Here's the one I meant: http://msdn.microsoft.com/en-us/library/aa453707.aspx

Seems your list is more comprehensive though. I tend to rely on MSDN for my API stuff. Generaly speaking MSDN is probably the best online resource for API.

Rather MSDN is the 2nd best resource, Jafaar Tribak's posts are my 1st choice.