System info in header/footer.

[Guest]

How can I reference system information, i.e. user name, computer name, and
other system info, to put them into the header and footer?
I would like to put the user name and computer name in the footer, for
document tracking purposes.

 

[Guest]

If you want to return the user name that the user has registered with the
application you can use:

ActiveUserName = Application.UserNameInsert

---------------------------------------------------------------------------------
To return the network user name use:

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

---------------------------------------------------------------------------------
To return the computer name use:

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long


Function ReturnComputerName() As String
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetComputerName(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
ReturnComputerName = UCase(Trim(tString))
End Function

 

[Bob Phillips]

Here is some code to put in ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sText As String
With ActiveSheet.PageSetup
sText = "User: " & CreateObject("Wscript.Network").UserName & _
", Computer: " &
CreateObject("Wscript.Network").ComputerName
.LeftFooter = sText
End With
End Sub

 

[Bob Phillips]

Shouldn't

ActiveUserName = Application.UserNameInsert

be

ActiveUserName = Application.UserName


And why6 doi you upshift the computer name and user name in those functions?

 

[Guest]

Is there a listing of attributes in those libraries that I can reference?

 

[David McRitchie]

Hi Josh,
Basically for headers and footers, I would think you would be interested in
Description VBA code Example
Full Name: Application.ActiveWorkbook.FullName D:\TestFolder\test.xls
Sheetname: application.activesheet.name Sheet1
More information in
Pathname in headings, footers, and cells
http://www.mvps.org/dmcritchie/excel/pathname.htm

But from the question you asked about other computer information:
Properties
http://www.mvps.org/dmcritchie/excel/property.htm

 

[Guest]

Oops...Yes it should be:

ActiveUserName = Application.UserName

Not quite sure what you mean by upshift the names???

 

[Guest]

Sorry Josh, I am not sure how to get a "list of attributes" for a .dll file.
You may want ot check the MS website. If you want info on the functions, you
can peruse the API functions at:

http://msdn.microsoft.com/library/d...g/winprog/functions_in_alphabetical_order.asp

 

[Guest]

Josh, if you don't like the uppercase names, just change:

UCase(Trim(tString))

to:

Trim(tString)

 

[Guest]

Ohhhh, you must mean the uppercase function. There is no need to use the
"UCase" function. This is a copy from previous code, and I must have needed
it for a strcmp or something. I guess I should look a little more closely
before posting old code. I hope I didn't cause Josh any confusion...

I'll post a note to Josh...