Getting hard disk physical serial numbers

There was a question at Eng-Tips about how to get the physical serial number of a hard disk (rather than the soft number, that is changed by a re-format): http://eng-tips.com/viewthread.cfm?qid=285948&page=1

A search found this discussion with the answer: http://thedailyreviewer.com/office/view/programmatically-obtain-the-hard-disks-serial-number-from-vba-101853101
(see answer number 8 )

from which I wrote the following code for an Excel User Defined Function (UDF) that will return the physical serial number of all the hard disks connected to the computer it is running on (enter as an array function to display data for more than one disk):

Function GetPhysicalSerial() As Variant

Dim obj As Object
Dim WMI As Object
Dim SNList() As String, i As Long, Count As Long

Set WMI = GetObject("WinMgmts:")

For Each obj In WMI.InstancesOf("Win32_PhysicalMedia")
If obj.SerialNumber <> "" Then
Count = Count + 1
Next

ReDim SNList(1 To Count, 1 To 1)

i = 1
For Each obj In WMI.InstancesOf("Win32_PhysicalMedia")
SNList(i, 1) = obj.SerialNumber
i = i + 1
If i > Count Then
 Exit For
Next

GetPhysicalSerial = SNList
End Function

References required for the function to work are shown in the screen shot below:

References for WinMgMts Object

I haven’t used the WinMgmts object before, but it seems that it will return a wide variety of other information about your hardware, although the usage is not particularly user-friendly.  The best source of information I could find was:

http://msdn.microsoft.com/en-us/library/aa394585

This entry was posted in Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

One Response to Getting hard disk physical serial numbers

  1. Pingback: Getting hard disk physical serial numbers | Newton Excel Bach, not … | StorageDrive.Org

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s