Document Properties

Sometimes there is a need/requirement to get the Document properties in particular the "Last Saved Date", "Last Saved Time", "Last Saved By", "Author", etc...

There are various codes that can be used, however I feel that using the code below is a lot easier as only needs to be written once:

Function DocProps(prop As String)
     '-----------------------------------------------------------------
    Application.Volatile
    On Error GoTo err_value
    DocProps = ActiveWorkbook.BuiltinDocumentProperties _
    (prop)
    Exit Function
err_value:
    DocProps = CVErr(xlErrValue)
End Function

Off course that we do need now to indentify the cells with what is required from the code so in your book and in the desired cell just enter the following:

• Last Saved Date =DocProps("Last Save Time") format as dd/mm/yy
• Last Saved Time =DocProps("Last Save Time") format as hh:mm:ss
• Last Saved By =DocProps("Last Author") format as General
• Author =DocProps("Author") format as General

Excel - Date Formulas

Excel is not very user friendly when he need work with dates, however there are simple functions which allow us to work with them, see some samples below.

Please remember to adjust the cells/range references to your excel book

• Define the number of days within a month:

Let's take November 2010 as an example;

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

The result will be 30 of course...

• Get the first day of the month:

This function may be useful if you want to do a reference to a Month but have "Raw Data" with diferent days...

Let's take the date of 25 November 2012 as an example;

=(DATE(YEAR(A1),MONTH(A1),1)

The result will be 01/11/2010 and if you "custom" format to mmm-yy it will be displayed Nov-10 in your cells

Get User Name (Windows Authentication Login)

Full Exel VBA to extract the Windows Authentication login

=======================================================
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function
========================================================

Short version Exel VBA to extract the Windows Authentication login

Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function

=========================================================

I remember using Environ to get the current location of the “My Documents“folder for the current user:

MsgBox Environ("USERPROFILE") + "\My Documents"

So having my memory jarred on the Environ function, I thought I would check VBA help to see what else this Little gem provided. And boy, how disappointing Help was... here is what it looks like: Environ Help.
Not too useful I thought... So I decided to figure it out on my own and loop thru all the arguments possible with Environ.
Copy and run this little routine to see all that Environ offers:

MsgBox Environ("USERPROFILE") + "\My Documents"Public Sub EnvironFunction()

Dim nCount As Integer
nCount = nCount + 1

Do Until Environ(nCount) = ""
Debug.Print Environ(nCount)
nCount = nCount + 1 Loop

End Sub

There are lots of useful things in there including APPDATA, COMPUTERNAME, HOMEDRIVE, HOMEPATH, OS, USERDOMAIN and more... Hopefully you will find it useful and I won't forget about it again.

****Nice to see blogging helps you remember what you forgot and that readers often help writers more than the other way around :)

Here's a complete list (that I know of) of the named arguments for the Environ Function:
Environ arguments

ALLUSERSPROFILE
PATHEXT
APPDATA
PROCESSOR_ARCHITECTURE
AVENGINE
PROCESSOR_IDENTIFIER
CLIENTNAME
PROCESSOR_LEVEL
CommonProgramFiles
PROCESSOR_REVISION
COMPUTERNAME
ProgramFiles
ComSpec
SESSIONNAME
FP_NO_HOST_CHECK
SystemDrive
HOMEDRIVE
SystemRoot
HOMEPATH
TEMP
INCLUDE
TMP
INOCULAN
USERDOMAIN
LIB
USERNAME
LOGONSERVER
USERPROFILE
NUMBER_OF_PROCESSORS
VS71COMNTOOLS
OS
WecVersionForRosebud.FF0
Path
windir