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

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

"Jenny B." wrote:

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

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." wrote in message
...
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

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

Have a great day - Jenny B.

"Bob Phillips" wrote:

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." wrote in message
...
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

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" wrote:

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." wrote in message
...
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

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)



"Jenny B." wrote in message
...
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" wrote:

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." wrote in message
...
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

Maybe using:

mypath = CreateObject("WScript.Shell").SpecialFolders("Desk Top")

Would work better for you.

Jenny B. wrote:

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" wrote:

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." wrote in message
...
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






--

Dave Peterson

Thank you - it's working just great now!

"Dave Peterson" wrote:

Maybe using:

mypath = CreateObject("WScript.Shell").SpecialFolders("Desk Top")

Would work better for you.

Jenny B. wrote:

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" wrote:

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." wrote in message
...
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






--

Dave Peterson