Get Computer Name and other useful VBA functions

I have a number of PeopleSoft nVision reports that have macros attached to them.  PS nVision on the client can be thought of as a wrapper around Microsoft Excel – so from this point on we’ll treat any nVision layout as an Excel workbook.

These workbooks are on different servers or can be run on a clients PC.  A lot of them use templates to create a final report.  The macros need the path to the templates – and while the directory structure in our development to QA to production environments all stay the same – it’s the computer name that obviously changes.

I have some helper functions that I use to help get information used by my various macros.

To get the computer name – in the General Declarations of the code module add:

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

I also declare some other variables:

Private Const MAX_COMPUTERNAME_LENGTH = 31
Private Const sNameUnknown = “NOT_FOUND”
Private mvalComputerName As String
Private mvalHaveValidName As Boolean

Then it’s all put together in this function:

Private Function ComputerNameIs() As String
    Dim lngLength As Long
    Dim lngResult As Long
    Dim strNameBuffer As String
    ‘Maximum Computer Name + Terminating Null Char
    lngLength = MAX_COMPUTERNAME_LENGTH + 1
    ‘Create Buffer
    strNameBuffer = String(lngLength, “X”)
    ‘Get the computer Name
    lngResult = GetComputerName(strNameBuffer, lngLength)
    If lngResult <> 0 Then
        ComputerNameIs = Mid(strNameBuffer, 1, lngLength)
        mvalHaveValidName = True
    Else
        ComputerNameIs = sNameUnknown
        mvalHaveValidName = False
    End If
   
End Function

I’ll be adding more in future posts.

Advertisements

About Lee Greffin
Just another programmer...

One Response to Get Computer Name and other useful VBA functions

  1. Stefano says:

    Good function but …
    I suggest to change the used font: single & double quotation are not recognized as valid chars when I paste the code into Excel VBA editor so It needs to be replaced everytime.
    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: