Using default file locations in VBA programming
 

I need to save an excel workbook in the default location for the user logged
onto the machine.

For example.
C:\Documents and Settings\username\My Documents
where username varies with different users and machines

I understand there are some shortcuts to these locations but cannot seem to
locate them in the documentation.
I am thinking something like this should work.

dim MydocumentsDir as string
mydocumentsDir = ?????????????

I then plan to add additional info like filename and a subdirectory to the
string.
and save my data in that folder.

Just cannot seem to find the code to replace the ??????????????

Gary M.

Using default file locations in VBA programming
 

This may help you towards your goal :-
'-------------------------------------
Sub RetrieveLogonName()
Dim wshNetwork As Object
Dim LogonName As Variant
Set wshNetwork = CreateObject("WScript.Network")
LogonName = wshNetwork.UserName
MsgBox LogonName
End Sub
'-------------------------------------

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Using default file locations in VBA programming
 

Brian

You got me started down the right path.

This is what wound up using.

Sub Desktop_Path()
Dim WSHShell As Object
Set WSHShell = CreateObject("WScript.Shell")
Dim DesktopPath As String

' Read desktop path using WshSpecialFolders object
DesktopPath = WSHShell.SpecialFolders("Desktop")
Debug.Print DesktopPath

DesktopPath = WSHShell.SpecialFolders("Mydocuments")
Debug.Print DesktopPath

Set WSHShell = Nothing

End Sub

hope you might find it usefull.

Thanks,
Gary m.



"BrianB" wrote in message
...

This may help you towards your goal :-
'-------------------------------------
Sub RetrieveLogonName()
Dim wshNetwork As Object
Dim LogonName As Variant
Set wshNetwork = CreateObject("WScript.Network")
LogonName = wshNetwork.UserName
MsgBox LogonName
End Sub
'--------------------------------------


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements