Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting system information
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rota system | Excel Discussion (Misc queries) | |||
Pivot table cutting information of a part of the information | Excel Discussion (Misc queries) | |||
Convert military date system to standard date system | Excel Discussion (Misc queries) | |||
How do I open an Excel file on XP system, saved on a Vista system | Excel Discussion (Misc queries) | |||
excel causing system to be in low system resource | Excel Discussion (Misc queries) |