GetSaveAsFilename - use Logged on username

[Rich96]

When a user closes a workbook I want my macro to retrieve the username
logged in to the machine and use that as the filename. Is this
possible?

 

[Dave Peterson]

In a general module:
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

'and code to save could look like:

Sub saveIt()
Dim myPath As String
Dim resp As Long
Dim myFileName As String

myPath = "C:\my documents\excel\"

myFileName = myPath & fOSUserName & ".xls"

If Dir(myFileName) = "" Then
resp = vbYes
Else
resp = MsgBox(Prompt:="Overwrite the existing file?", Buttons:=vbYesNo)
End If

If resp = vbYes Then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlNormal
Application.DisplayAlerts = True
Else
MsgBox "Please try later!"
End If

End Sub


But I'm not sure how you're going to fit getsaveasfilename into it.