Desktop Save Question

[Jenny B.]

Hi All,

I'm setting up a macro that will be used by multiple users. One of the
pieces consists of placing a copy of the form to the users desktop. My
question is - how can I set up the desktop address to save to random users
vs. the standard single user address? Right now the below is just referring
just one person (MyName) and I need it to be generic and encompass whoever is
using the application and not be specific.

Thank you in advance - Jenny B.

Sub DesktopSave()

Dim mypath As String
Dim nrng As Range
Dim fname As String
Set nrng = Range("H5")
ActiveSheet.Copy
mypath = "C:\Documents and Settings\MyName\Desktop"
fname = nrng.Value & ".xls"
ActiveWorkbook.SaveAs filename:= _
mypath & fname, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call CLEAR
End Sub

 

[Mike]

Place at the top of you module
Private Declare Function GetUserName Lib _
"advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
I use this function and you could pass to your mypath
mypath = "C:\Documents and Settings\" & UserNameWindows & "\Desktop"
Private Function UserNameWindows() As String
Dim lngLen As Long
Dim strBuffer As String
Const dhcMaxUserName = 255
strBuffer = Space(dhcMaxUserName)
lngLen = dhcMaxUserName
If CBool(GetUserName(strBuffer, lngLen)) Then
UserNameWindows = Left$(strBuffer, lngLen - 1)
Else
UserNameWindows = ""
End If
End Function

 

[Bob Phillips]

Sub DesktopSave()

Dim mypath As String
Dim nrng As Range
Dim fname As String
Set nrng = Range("H5")
ActiveSheet.Copy
mypath = CreateObject("WScript.Shell").SpecialFolders(10)
fname = nrng.Value & ".xls"
ActiveWorkbook.SaveAs Filename:= _
mypath & fname, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call Clear
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jenny B.]

Thank you both. Both ideas will work great and I really appreciate all of
your help.

Have a great day - Jenny B.

 

[Jenny B.]

Good Morning

One more quick question. This code places it one step outside of the
Desktop folder (see the below path). Is there any way to direct it inside
the Desktop folder?

Thanks again - Jenny B.

C:\Documents and Settings\MyName

 

[Bob Phillips]

That give me

C:\Documents and Settings\Bob\Desktop

Try entering this

?CreateObject("WScript.Shell").SpecialFolders(10)

in the Immediate window in the VBIDE, maybe changing the value if it isn't
correct.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Maybe using:

mypath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")

Would work better for you.

 

[Jenny B.]

Thank you - it's working just great now!

Maybe using:

mypath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")

Would work better for you.