Output file to My Documents

[scdallas]

I'm trying to write a macro in a file located on a shared drive that will be
used by various members of my team. It needs to Open a file for Output and
I'd like it to save the file to the My Documents folder of whoever is running
the macro. Is there a way to do this either using the UserName in the file
name or can "My Documents" be identified without putting a full path (ie
c:\Documents and Settings\username\My Documents\...)? I'm using Excel 2003.

 

[Gary Keramidas]

this has always worked for me. some say it doesn't, but i've never had any
issues.

environ("UserProfile") & "\My Documents\"

 

[JP]

Modified from an old Dave Peterson post:

Dim strMyDoc As String

strMyDoc = MyDocPath

Function MyDocPath() As String
' returns path to "My Documents" as a String
Dim WSHShell As Object

Set WSHShell = CreateObject("WScript.Shell")
MyDocPath = WSHShell.SpecialFolders("MyDocuments")

Set WSHShell = Nothing
End Function

ActiveWorkbook.SaveAs strMyDoc & "\" & "myfile.xls"


--JP

 

[scdallas]

Thanks so much! It worked for me! I simply substituted
environ("UserProfile") in the portion of my Open statement where I had
hard-coded the location of MY My Documents folder and sure enough, my output
file was where I expected it to be!

Sue