Change Save Code to work for other Users

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi:

Once again I seek the help of the experts :)

I am trying to alter this code so that it will save to the user desktop. Right now it is saving to MY desktop. How can I make it User Specific? I want the HiginM to be whatever the users name is

Code:
Dim SaveMeAs As String
    SaveMeAs = Sheets("ARIR Template-Electronic").Range("CustomerName").Text
 
Sheets("Customer Setup").Copy
 
    ActiveSheet.Unprotect "password"
 
ActiveSheet.Cells.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
 
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\[COLOR=red][B]HiginM[/B][/COLOR]\Desktop\Test\" & SaveMeAs

Any suggestions? :confused:

THANKS,
Mark
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Maybe this
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\"&Environ("Username")&"\Desktop\Test\" & SaveMeAs
 

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
This is from MSDN-should work

http://support.microsoft.com/kb/161394

Code:
Declare Function WNetGetUser Lib "mpr.dll" _
      Alias "WNetGetUserA" (ByVal lpName As String, _
      ByVal lpUserName As String, lpnLength As Long) As Long
   Const NoError = 0       'The Function call was successful
Private Sub GetUserName()
      ' Buffer size for the return string.
      Const lpnLength As Integer = 255
      ' Get return buffer space.
      Dim status As Integer
      ' For getting user information.
      Dim lpName, lpUserName As String
      ' Assign the buffer size constant to lpUserName.
      lpUserName = Space$(lpnLength + 1)
      ' Get the log-on name of the person using product.
      status = WNetGetUser(lpName, lpUserName, lpnLength)
      ' See whether error occurred.
      If status = NoError Then
         ' This line removes the null character. Strings in C are null-
         ' terminated. Strings in Visual Basic are not null-terminated.
         ' The null character must be removed from the C strings to be used
         ' cleanly in Visual Basic.
         lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
         
      Else
         ' An error occurred.
         MsgBox "Unable to get the name."
         End
      End If
      ' Display the name of the person logged on to the machine.
      MsgBox "The person logged on this machine is: " & lpUserName
End Sub
 

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
ohhhhh - be aware - Windows 7 uses a a folder c:\users & not documents and settings
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
THANKS to BOTH of your for your replies and suggestions. I will give them a try tomorrow when I return to the office. None of my users are on Windows 7 yet. I believe they are all using XP. THANKS again :biggrin:

Have a GREAT night,
Mark :)
 

Forum statistics

Threads
1,141,734
Messages
5,708,166
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top