Locate the My Documents folder with VBA

[neil.mclean]

I need to save files in "My Documents" regardless of where it is. How can I
use vba to locate the "My Documents folder" being used and save the file to
it?

Thanks in advance

 

[Jay Freedman]

I need to save files in "My Documents" regardless of where it is. How can I
use vba to locate the "My Documents folder" being used and save the file to
it?

Thanks in advance

Unless somebody comes up with a better method, you can dig it out of
the registry like this:

Dim MyDocsPath As String

MyDocsPath = System.PrivateProfileString( _
FileName:="", _
Section:="HKEY_CURRENT_USER\Software\Microsoft\" _
& "Windows\CurrentVersion\Explorer\Shell Folders", _
Key:="Personal")

MsgBox MyDocsPath

 

[Philippe L. Balmanno]

I need to save files in "My Documents" regardless of where it is. How can I
use vba to locate the "My Documents folder" being used and save the file
to
it?

Thanks in advance

Make it a VBS script in 5 lines:

Const MY_DOCUMENTS = &H5&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self
Wscript.Echo objFolderItem.Name & ": " & objFolderItem.Path

 

[David Sisson]

Would this be it?

MyPath = Options.DefaultFilePath(wdDocumentsPath)

 

[Jay Freedman]

Would this be it?

MyPath = Options.DefaultFilePath(wdDocumentsPath)

Not necessarily. That retrieves the current setting of the "Documents" item
in the Tools > Options > File Locations dialog. By default that points to
the My Documents folder, but it can be changed by the user to point to any
folder.

 

[neil.mclean]

Thank you all for replying. I will test the solutions provided however this
is the solution that has been adopted and works for my application - in case
someone else needs the answer

' Get MyDocuments folder path
Dim MyDocumentsDirectory
Dim wshShell
Set wshShell = CreateObject("WScript.Shell") MyDocumentsDirectory =
wshShell.SpecialFolders("MyDocuments")