We need to collect information about each of the PCs in our department.
I thought it might be possible to get it by sending a spreadsheet to
each user, which will run a macro to collect the info. I am aware of
the "Environ" function in VBA and have used it in the past to get the
system username but I cannot find anything that tells me what other info
I can gather.

The information that we want from each PC is:

Username (So we know who's PC it is - this much I can already do.)
The name of the PC on the system.
The operating system.
The amount of RAM in the PC.
The type of processor including speed.
The size of the hard disk (needs to be total size of all hard drives,
bearing in mind that the drive might be partitioned or there may be more
than one physical hard drive).

To be honest I don't even know if it is possible for Excel and VBA to
get this info. Can anybody help?

All the PCs are running Office 97. Most use Windows NT4 but some use
Windows 2000.

Thanks,

Graham Standring
University of Sunderland
England

I do not know the name of all the functions but you will need to use
windows API calls. Do a search for the question and API, a lot of
results should show up.

Keith
www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/

Hi,

I could tell you the names of the API's but declarations are not the
only problem. The usage of the API's are mostly a little bit komplex,
if someone never used API's.

So I would recommend an application (API-Guide V3.7) or may be , which
is currently explaining all of the Windows API's (except Windows XP)
including at least an exaple for each.

You can download this application at http://www.allapi.net


---
Message posted from http://www.ExcelForum.com/

Graham,

Some toughies here but here goes

'---------------------------------------------------------------------------
----------------------------------------------
OS: simple coommand

Application.Operating System

'---------------------------------------------------------------------------
----------------------------------------------
Memory: need access to the GetMemoryStatus API here

Private Type MEMORYSTATUS
dwLength As Long
dwMemoryLoad As Long
dwTotalPhys As Long
dwAvailPhys As Long
dwTotalPageFile As Long
dwAvailPageFile As Long
dwTotalVirtual As Long
dwAvailVirtual As Long
End Type

Private Declare Sub GlobalMemoryStatus Lib "kernel32" _
(lpBuffer As MEMORYSTATUS)


Sub GetMemory()
Dim MS As MEMORYSTATUS

MS.dwLength = Len(MS)
GlobalMemoryStatus MS

MsgBox "Percentage used: " & Format(MS.dwMemoryLoad,
"###,###,###,###") & " % used" & vbCrLf & _
"Total physical memory: " & Format(MS.dwTotalPhys /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total available memory: " & Format(MS.dwAvailPhys /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total total page file: " & Format(MS.dwTotalPageFile /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total avaiulable page file: " & Format(MS.dwAvailPageFile /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total total virtual memory: " & Format(MS.dwTotalVirtual /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total available virtual memory: " & Format(MS.dwAvailVirtual /
1024, "###,###,###,###") & " Kbyte" & vbCrLf
End Sub

'---------------------------------------------------------------------------
----------------------------------------------
Drive size: you need to set a reference to the Microsoft WMI Scripting
Library and run this simple routine

Dim oDrives As SWbemObjectSet
Dim dd As SWbemObject

Set oDrives = GetObject("winmgmts:{impersonationLevel=impersonat e}"). _
InstancesOf("Win32_DiskDrive")

For Each dd In oDrives
MsgBox "Disk size is " & FormatNumber(dd.Size, 0) & " has " &
dd.Partitions & " partitions"
Next

'---------------------------------------------------------------------------
----------------------------------------------
Processor Info: again, need API call to GetSystemInfo, and read the registry

'Flags for GetSystemInfo
Private Const PROCESSOR_INTEL_386 As Long = 386
Private Const PROCESSOR_INTEL_486 As Long = 486
Private Const PROCESSOR_INTEL_PENTIUM As Long = 586
Private Const PROCESSOR_MIPS_R4000 As Long = 4000
Private Const PROCESSOR_ALPHA_21064 As Long = 21064
Private Const PROCESSOR_PPC_601 As Long = 601
Private Const PROCESSOR_PPC_603 As Long = 603
Private Const PROCESSOR_PPC_604 As Long = 604
Private Const PROCESSOR_PPC_620 As Long = 620
Private Const PROCESSOR_HITACHI_SH3 As Long = 10003 'Windows CE
Private Const PROCESSOR_HITACHI_SH3E As Long = 10004 'Windows CE
Private Const PROCESSOR_HITACHI_SH4 As Long = 10005 'Windows CE
Private Const PROCESSOR_MOTOROLA_821 As Long = 821 'Windows CE
Private Const PROCESSOR_SHx_SH3 As Long = 103 'Windows CE
Private Const PROCESSOR_SHx_SH4 As Long = 104 'Windows CE
Private Const PROCESSOR_STRONGARM As Long = 2577 'Windows CE - 0xA11
Private Const PROCESSOR_ARM720 As Long = 1824 'Windows CE - 0x720
Private Const PROCESSOR_ARM820 As Long = 2080 'Windows CE - 0x820
Private Const PROCESSOR_ARM920 As Long = 2336 'Windows CE - 0x920
Private Const PROCESSOR_ARM_7TDMI As Long = 70001 'Windows CE

Private Const PROCESSOR_ARCHITECTURE_INTEL As Long = 0
Private Const PROCESSOR_ARCHITECTURE_MIPS As Long = 1
Private Const PROCESSOR_ARCHITECTURE_ALPHA As Long = 2
Private Const PROCESSOR_ARCHITECTURE_PPC As Long = 3
Private Const PROCESSOR_ARCHITECTURE_SHX As Long = 4
Private Const PROCESSOR_ARCHITECTURE_ARM As Long = 5
Private Const PROCESSOR_ARCHITECTURE_IA64 As Long = 6
Private Const PROCESSOR_ARCHITECTURE_ALPHA64 As Long = 7
Private Const PROCESSOR_ARCHITECTURE_UNKNOWN As Long = &HFFFF&

Private Const PROCESSOR_LEVEL_80386 As Long = 3
Private Const PROCESSOR_LEVEL_80486 As Long = 4
Private Const PROCESSOR_LEVEL_PENTIUM As Long = 5
Private Const PROCESSOR_LEVEL_PENTIUMII As Long = 6

Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\ 0"

Private Const HKEY_LOCAL_MACHINE As Long = &H80000002

Private Type SYSTEM_INFO
dwOemID As Long
dwPageSize As Long
lpMinimumApplicationAddress As Long
lpMaximumApplicationAddress As Long
dwActiveProcessorMask As Long
dwNumberOfProcessors As Long
dwProcessorType As Long
dwAllocationGranularity As Long
wProcessorLevel As Integer
wProcessorRevision As Integer
End Type

Private Declare Sub GetSystemInfo Lib "kernel32" _
(lpSystemInfo As SYSTEM_INFO)

Private Declare Function RegCloseKey Lib "advapi32" _
(ByVal hKey As Long) As Long

Private Declare Function RegOpenKey Lib "advapi32" _
Alias "RegOpenKeyA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32" _
Alias "RegQueryValueExA" _
(ByVal hKey As Long, _
ByVal lpValueName As String, _
ByVal lpReserved As Long, _
lpType As Long, _
lpData As Any, _
lpcbData As Long) As Long

Sub GetProcessorInfo()
Dim SI As SYSTEM_INFO
Dim tmp1 As String
Dim tmp2 As String

Call GetSystemInfo(SI)

Select Case SI.dwProcessorType
Case PROCESSOR_INTEL_386: tmp1 = "386"
Case PROCESSOR_INTEL_486: tmp1 = "486"
Case PROCESSOR_INTEL_PENTIUM: tmp1 = "Pentium"
Case PROCESSOR_MIPS_R4000: tmp1 = "MIPS 4000"
Case PROCESSOR_ALPHA_21064: tmp1 = "Alpha"
End Select

Select Case SI.wProcessorLevel
Case PROCESSOR_LEVEL_80386: tmp2 = "Intel 80386"
Case PROCESSOR_LEVEL_80486: tmp2 = "Intel 80486"
Case PROCESSOR_LEVEL_PENTIUM: tmp2 = "Intel Pentium"
Case PROCESSOR_LEVEL_PENTIUMII: tmp2 = "Intel Pentium Pro, II, III
or 4"
End Select

MsgBox "Number Of Processors " & SI.dwNumberOfProcessors & vbCrLf & _
"Processor Type " & SI.dwProcessorType & " " & tmp1 & vbCrLf & _
"Processor Level " & SI.wProcessorLevel & " " & tmp2 & vbCrLf & _
"Processor Revision " & SI.wProcessorRevision & vbCrLf & _
"Model " & HiByte(SI.wProcessorRevision) & vbCrLf & _
"Stepping " & LoByte(SI.wProcessorRevision) & vbCrLf & _
"CPU Speed" & " " & GetCPUSpeed() & " MHz"

End Sub

Public Function HiByte(ByVal wParam As Integer) As Byte

HiByte = (wParam And &HFF00&) \ (&H100)

End Function

Public Function LoByte(ByVal wParam As Integer) As Byte

LoByte = wParam And &HFF&

End Function

Private Function GetCPUSpeed() As Long
Dim hKey As Long
Dim nCPUSpeed As Long

Call RegOpenKey(HKEY_LOCAL_MACHINE, sCPURegKey, hKey)

Call RegQueryValueEx(hKey, "~MHz", 0, 0, nCPUSpeed, 4)
Call RegCloseKey(hKey)

GetCPUSpeed = nCPUSpeed

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Graham Standring" wrote in message
...
We need to collect information about each of the PCs in our department.
I thought it might be possible to get it by sending a spreadsheet to
each user, which will run a macro to collect the info. I am aware of
the "Environ" function in VBA and have used it in the past to get the
system username but I cannot find anything that tells me what other info
I can gather.

The information that we want from each PC is:

Username (So we know who's PC it is - this much I can already do.)
The name of the PC on the system.
The operating system.
The amount of RAM in the PC.
The type of processor including speed.
The size of the hard disk (needs to be total size of all hard drives,
bearing in mind that the drive might be partitioned or there may be more
than one physical hard drive).

To be honest I don't even know if it is possible for Excel and VBA to
get this info. Can anybody help?

All the PCs are running Office 97. Most use Windows NT4 but some use
Windows 2000.

Thanks,

Graham Standring
University of Sunderland
England

Thanks everybody for your help.

Bob, your codes worked fine except for the one that gives the drive
size. That is because the Microsoft WMI scripting Library is not
installed with Windows NT. I found another solution at the
www.allapi.net website that "tolgag" mentioned. What a fantastic
resource that is.

Thanks again.

Graham

Bob Phillips wrote:

Graham,

Some toughies here but here goes

'---------------------------------------------------------------------------
----------------------------------------------
OS: simple coommand

Application.Operating System

'---------------------------------------------------------------------------
----------------------------------------------
Memory: need access to the GetMemoryStatus API here

Private Type MEMORYSTATUS
dwLength As Long
dwMemoryLoad As Long
dwTotalPhys As Long
dwAvailPhys As Long
dwTotalPageFile As Long
dwAvailPageFile As Long
dwTotalVirtual As Long
dwAvailVirtual As Long
End Type

Private Declare Sub GlobalMemoryStatus Lib "kernel32" _
(lpBuffer As MEMORYSTATUS)


Sub GetMemory()
Dim MS As MEMORYSTATUS

MS.dwLength = Len(MS)
GlobalMemoryStatus MS

MsgBox "Percentage used: " & Format(MS.dwMemoryLoad,
"###,###,###,###") & " % used" & vbCrLf & _
"Total physical memory: " & Format(MS.dwTotalPhys /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total available memory: " & Format(MS.dwAvailPhys /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total total page file: " & Format(MS.dwTotalPageFile /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total avaiulable page file: " & Format(MS.dwAvailPageFile /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total total virtual memory: " & Format(MS.dwTotalVirtual /
1024, "###,###,###,###") & " Kbyte" & vbCrLf & _
"Total available virtual memory: " & Format(MS.dwAvailVirtual /
1024, "###,###,###,###") & " Kbyte" & vbCrLf
End Sub

'---------------------------------------------------------------------------
----------------------------------------------
Drive size: you need to set a reference to the Microsoft WMI Scripting
Library and run this simple routine

Dim oDrives As SWbemObjectSet
Dim dd As SWbemObject

Set oDrives = GetObject("winmgmts:{impersonationLevel=impersonat e}"). _
InstancesOf("Win32_DiskDrive")

For Each dd In oDrives
MsgBox "Disk size is " & FormatNumber(dd.Size, 0) & " has " &
dd.Partitions & " partitions"
Next

'---------------------------------------------------------------------------
----------------------------------------------
Processor Info: again, need API call to GetSystemInfo, and read the registry

'Flags for GetSystemInfo
Private Const PROCESSOR_INTEL_386 As Long = 386
Private Const PROCESSOR_INTEL_486 As Long = 486
Private Const PROCESSOR_INTEL_PENTIUM As Long = 586
Private Const PROCESSOR_MIPS_R4000 As Long = 4000
Private Const PROCESSOR_ALPHA_21064 As Long = 21064
Private Const PROCESSOR_PPC_601 As Long = 601
Private Const PROCESSOR_PPC_603 As Long = 603
Private Const PROCESSOR_PPC_604 As Long = 604
Private Const PROCESSOR_PPC_620 As Long = 620
Private Const PROCESSOR_HITACHI_SH3 As Long = 10003 'Windows CE
Private Const PROCESSOR_HITACHI_SH3E As Long = 10004 'Windows CE
Private Const PROCESSOR_HITACHI_SH4 As Long = 10005 'Windows CE
Private Const PROCESSOR_MOTOROLA_821 As Long = 821 'Windows CE
Private Const PROCESSOR_SHx_SH3 As Long = 103 'Windows CE
Private Const PROCESSOR_SHx_SH4 As Long = 104 'Windows CE
Private Const PROCESSOR_STRONGARM As Long = 2577 'Windows CE - 0xA11
Private Const PROCESSOR_ARM720 As Long = 1824 'Windows CE - 0x720
Private Const PROCESSOR_ARM820 As Long = 2080 'Windows CE - 0x820
Private Const PROCESSOR_ARM920 As Long = 2336 'Windows CE - 0x920
Private Const PROCESSOR_ARM_7TDMI As Long = 70001 'Windows CE

Private Const PROCESSOR_ARCHITECTURE_INTEL As Long = 0
Private Const PROCESSOR_ARCHITECTURE_MIPS As Long = 1
Private Const PROCESSOR_ARCHITECTURE_ALPHA As Long = 2
Private Const PROCESSOR_ARCHITECTURE_PPC As Long = 3
Private Const PROCESSOR_ARCHITECTURE_SHX As Long = 4
Private Const PROCESSOR_ARCHITECTURE_ARM As Long = 5
Private Const PROCESSOR_ARCHITECTURE_IA64 As Long = 6
Private Const PROCESSOR_ARCHITECTURE_ALPHA64 As Long = 7
Private Const PROCESSOR_ARCHITECTURE_UNKNOWN As Long = &HFFFF&

Private Const PROCESSOR_LEVEL_80386 As Long = 3
Private Const PROCESSOR_LEVEL_80486 As Long = 4
Private Const PROCESSOR_LEVEL_PENTIUM As Long = 5
Private Const PROCESSOR_LEVEL_PENTIUMII As Long = 6

Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\ 0"

Private Const HKEY_LOCAL_MACHINE As Long = &H80000002

Private Type SYSTEM_INFO
dwOemID As Long
dwPageSize As Long
lpMinimumApplicationAddress As Long
lpMaximumApplicationAddress As Long
dwActiveProcessorMask As Long
dwNumberOfProcessors As Long
dwProcessorType As Long
dwAllocationGranularity As Long
wProcessorLevel As Integer
wProcessorRevision As Integer
End Type

Private Declare Sub GetSystemInfo Lib "kernel32" _
(lpSystemInfo As SYSTEM_INFO)

Private Declare Function RegCloseKey Lib "advapi32" _
(ByVal hKey As Long) As Long

Private Declare Function RegOpenKey Lib "advapi32" _
Alias "RegOpenKeyA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32" _
Alias "RegQueryValueExA" _
(ByVal hKey As Long, _
ByVal lpValueName As String, _
ByVal lpReserved As Long, _
lpType As Long, _
lpData As Any, _
lpcbData As Long) As Long

Sub GetProcessorInfo()
Dim SI As SYSTEM_INFO
Dim tmp1 As String
Dim tmp2 As String

Call GetSystemInfo(SI)

Select Case SI.dwProcessorType
Case PROCESSOR_INTEL_386: tmp1 = "386"
Case PROCESSOR_INTEL_486: tmp1 = "486"
Case PROCESSOR_INTEL_PENTIUM: tmp1 = "Pentium"
Case PROCESSOR_MIPS_R4000: tmp1 = "MIPS 4000"
Case PROCESSOR_ALPHA_21064: tmp1 = "Alpha"
End Select

Select Case SI.wProcessorLevel
Case PROCESSOR_LEVEL_80386: tmp2 = "Intel 80386"
Case PROCESSOR_LEVEL_80486: tmp2 = "Intel 80486"
Case PROCESSOR_LEVEL_PENTIUM: tmp2 = "Intel Pentium"
Case PROCESSOR_LEVEL_PENTIUMII: tmp2 = "Intel Pentium Pro, II, III
or 4"
End Select

MsgBox "Number Of Processors " & SI.dwNumberOfProcessors & vbCrLf & _
"Processor Type " & SI.dwProcessorType & " " & tmp1 & vbCrLf & _
"Processor Level " & SI.wProcessorLevel & " " & tmp2 & vbCrLf & _
"Processor Revision " & SI.wProcessorRevision & vbCrLf & _
"Model " & HiByte(SI.wProcessorRevision) & vbCrLf & _
"Stepping " & LoByte(SI.wProcessorRevision) & vbCrLf & _
"CPU Speed" & " " & GetCPUSpeed() & " MHz"

End Sub

Public Function HiByte(ByVal wParam As Integer) As Byte

HiByte = (wParam And &HFF00&) \ (&H100)

End Function

Public Function LoByte(ByVal wParam As Integer) As Byte

LoByte = wParam And &HFF&

End Function

Private Function GetCPUSpeed() As Long
Dim hKey As Long
Dim nCPUSpeed As Long

Call RegOpenKey(HKEY_LOCAL_MACHINE, sCPURegKey, hKey)

Call RegQueryValueEx(hKey, "~MHz", 0, 0, nCPUSpeed, 4)
Call RegCloseKey(hKey)

GetCPUSpeed = nCPUSpeed

End Function

Graham,

I thought we had lost you.

WMI Library. When you say not installed, do you mean that, or just that
there is no reference within VBA. I did mention that you would need to set a
reference to it in the VBE.

Allapi is a fantastic site, but unfortunately it is no longer maintained.
Must have taken too much of the guys time.

Bob


"Graham Standring" wrote in message
...
Thanks everybody for your help.

Bob, your codes worked fine except for the one that gives the drive
size. That is because the Microsoft WMI scripting Library is not
installed with Windows NT. I found another solution at the
www.allapi.net website that "tolgag" mentioned. What a fantastic
resource that is.

Thanks again.

Graham