Using default file locations in VBA programming

[Gary W. Misner]

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.

 

[BrianB]

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
'-------------------------------------

 

[Gary W. Misner]

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.

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
'--------------------------------------


------------------------------------------------



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

creating financial statements