Desktop Save Question

J

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
 
M

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
 
B

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)
 
J

Jenny B.

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

Have a great day - Jenny B.
 
J

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
 
B

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)
 
D

Dave Peterson

Maybe using:

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

Would work better for you.
 
J

Jenny B.

Thank you - it's working just great now!

Dave Peterson said:
Maybe using:

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

Would work better for you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Date to File Name 3
Format Copy to Worksheet 0
Format Copy to Worksheet 1
2007 Excel vs. 2003 2
2007 Excel Issues 0
2007 Excel Issues 0
writerespassword is not working 1
Save file as Cell Value 2

Top