asked on

GetComputerNameA for Excel 2010 (64-bit)

I am trying to update a macro that gets the name of the computer running the macro.
I need it to work on both 32-bit Excel 2003 & 64-bit Excel 2010.
The attached code works in Excel 2003 & 32-bit Excel 2010 , but this gives me a 'Type mismatch' error when it is run on a 64-bit Excel 2010 PC.
Thanks for the help.

#If VBA7 Then           'Code is running in the new VBA7 editor
     #If Win64 Then     'Code is running in 64-bit version of Microsoft Office
        'API Declarations for Machine number
        Private Declare PtrSafe Function GetComputerName Lib "kernel32" Alias _
            "GetComputerNameA" (ByVal lpBuffer As String, nSize As LongPtr)
        
        Dim ComputerNameLen As LongPtr, Result As LongPtr
     #Else              'Code is running in 32-bit version of Microsoft Office
        Private Declare Function GetComputerName Lib "kernel32" _
          Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
     
        Dim ComputerNameLen As Long, Result As Long
     #End If
#Else
    'Code is running in VBA version 6 or earlier, Syntax is same as above
    Private Declare Function GetComputerName Lib "kernel32" _
        Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Dim ComputerNameLen As Long, Result As Long
#End If


Sub Get_PCnumber()
    Dim ComputerName As String
    ComputerNameLen = 256
    ComputerName = Space(ComputerNameLen)
    If GetComputerName(ComputerName, ComputerNameLen) <> 0 Then
        ComputerName = Left(ComputerName, ComputerNameLen)
    Else
        ComputerName = ""
    End If
    MsgBox ComputerName
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jeff Geiselman

ASKER

Thanks, that worked after I also changed
Dim ComputerNameLen As LongPtr
back to
Dim ComputerNameLen As Long

My actual macro also uses 'GetWindowsDirectoryA' which needs the 64-bit LongPtr. I should not have assumed both functions needed 'LongPtr'.

Thanks again for for quick response and correct syntax!

Jeff Geiselman

ASKER

Solution was very close to what I needed and easily completed.