What code can I use in VBA to find out the computer name on which the application is running?

[LGuzman]

__________________
[LGuzman]

Option Compare Database

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

Public Sub atCName()
    On Error Resume Next

    Dim NBuffer As String
    Dim Buffsize As Long
    Dim Wok As Long
    Dim strComputerName As String

    Buffsize = 256
    NBuffer = Space$(Buffsize)

    Wok = api_GetComputerName(NBuffer, Buffsize)
    strComputerName = Trim$(NBuffer)

    Debug.Print strComputerName

End Sub

HTH,

Bob

Hi Bob.

Thanks for your answer. However I am unable to get it to work. Where exactly do I put the code you provided me? In the Main Module or within the code that can be activated by a Command Button? I tried both places but it just doesn't seem to work.

Thanks again.

LG

[LGuzman]

Hi LG,

Just for the sake of keeping it all in one place, I'll declare everything as private and put it all in a form module with a command button (cmdGetComputerName) and a textbox (txtComputer). Pressing the command button loads my computer name into the text box.

The only restriction on code location is that you can't have public Declare statments in object modules (e.g., forms).

Option Compare Database

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

Private Sub cmdGetComputerName_Click()
    Dim strComputerName As String

    strComputerName = ComputerName
    Me.txtComputer = (strComputerName)

End Sub

Private Function ComputerName() As String
    On Error Resume Next

    Dim NBuffer As String
    Dim Buffsize As Long
    Dim Wok As Long
    Dim strComputerName As String

    Buffsize = 256
    NBuffer = Space$(Buffsize)

    Wok = api_GetComputerName(NBuffer, Buffsize)
    strComputerName = Trim$(NBuffer)

    ComputerName = (strComputerName)

End Function


What type of error message, if any, do you get?

Bob

Hi Bob.

I'm getting: "The expression On Open you entered as the event property setting produced the following error: Only comments may appear after End Sub, End Function, or End Property."

I think the problem is with the following part:

Option Compare Database

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

It doesn't seem to get a section for it self. It remains in the previous section under the End Sub line.

LG

[LGuzman]

Hi LG,

The Declare statement should be placed in your module's General Declaration section.

The first two lines of your module are probably:

Option Compare Database
Option Explicit

Place the Declare statement immediately after these two lines and before any other code.

Does that get it? Also, watch out for duplicate End Sub statements when pasting.

Bob

Thanks for your guidance Bob. It seems to be working much better now, except that in the text box I don't seem to get the computer name. I get "True". It should be "MtlAccounts".

Thanks again for your help.


LG

[LGuzman]

Bob,

Thank you very much for all your help. It works now. I found my mistake. I had entered:

             ComputerName = strComputerName = strComputerName

when it should had been

                       ComputerName = strComputerName

ComputerName is the name of the text box I use to display the computer name.

Once again thank you.


LG

[LGuzman]

My pleasure, LG. Glad you got it going OK.

Bob