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

LVL 1
Jeff GeiselmanIT Tech AssocAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
Try this

#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 Long) as Long

Sid

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff GeiselmanIT Tech AssocAuthor Commented:
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 GeiselmanIT Tech AssocAuthor Commented:
Solution was very close to what I needed and easily completed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.