beaswax19

WMI to Excel

2 posts in this topic

Hello, 

I'm trying to get some data from WMI and output a particular Object Value to an Excel Cell. I have used the scriptomatic to check out the WMI code but I'm a bit stuck on the Exporting a portion of the data to an Excel Cell. For Example. I am querying Win32_ComputerSystemProduct. I want to get the IdentifyingNumber and export the Value to "B2" in excel. Im new to Autoit and have searched the forums but cannot find help. Can someone at least give me assistance on what I need to do to export the value to excel. 

 

Thank you in advance, 

John

Share this post


Link to post
Share on other sites



Hi, @beaswax19. Here is a quick and dirty to give you an idea of how you could go about it. If you're planning on doing more with the resultant spreadsheet, I would declare all the variables ahead of time.

#include <Excel.au3>

$wbemFlagReturnImmediately = 0x10
$wbemFlagForwardOnly = 0x20
Local $ID

$oWMI = ObjGet("winmgmts:\\.\root\CIMV2")
$oItems = $oWMI.ExecQuery("SELECT * FROM Win32_ComputerSystemProduct", "WQL", $wbemFlagReturnImmediately + $wbemFlagForwardOnly)

    If IsObj($oItems) then
       For $item In $oItems
          $ID = $item.IdentifyingNumber
       Next
    Else
        MsgBox($MB_OK, "My Test", "Unable to query WMI")
    EndIf

    If $ID <> "" Then
        $oExcel = _Excel_Open()
        $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\my.xls")
        _Excel_RangeWrite($oWorkbook, Default, $ID, "B2")
    Else
        MsgBox($MB_OK, "My Test", "Identifying Number not found")
    EndIf

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • ModemJunki
      By ModemJunki
      Edit: I found out from looking into the post from coffeeturtle that not all motherboards support WMI temperature reading. You have to explore the WMI namespaces for your system!
      I (stupidly) fried a computer. It was an old Zotac small form factor device that was junked at work, so I turned it into a home theater PC.
      But I had put it in my "audio rack", which has glass doors and not very good ventilation. Normally I would open the doors while it was running and I use a media remote which would hibernate the system. At some point, it woke up by itself (probably Windows Update) and did not go to sleep again. Unfortunately this happened during the summer months while I was away on a small holiday and of course the glass doors were closed.
      When I finally noticed the system was on, it was super hot and the fan was at full blast. Dead. No POST, no green light on the mainboard even with a new power supply, old mechanical laptop hard drive gives errors when connected to another device. My next home project will be to make some appropriate ventilation in the audio cabinet.
      For the replacement I bought a used HP ultra small form factor machine and decided to start working on an app to monitor the ambient temperature sensor on the motherboard and shutdown the system if needed. I used some code for smooth label updates from here (needed updating to work with latest AutoIT) and temperature conversion from here (not really needed, only if you want temperatures in something other than C).
      Maybe I will work on this some more and make it configurable with an .INI file or even storing the settings in registry, but since I probably won't I thought I'd put it up as an example of what a non-expert can do with an afternoon of coding and ideas from the community here.
      This uses an ambient temperature sensor populated on HP machines in HP-specific WMI extensions but the WMI query can easily be changed to any available sensor including CPU or GPU.
      The watchdog monitors temperature, warns with S.O.S beeping if the set point is exceeded, and either shuts down if a timeout is reached while the temperatures are high or goes back to monitoring if the temperature goes lower then the set point, and logs events to the Windows application event logs.
      The GUI it puts up is very small at the top center of the screen (very small on a 4k display anyway).
      #requireadmin because of the WMI query.
      A timer for the WMI query because of prior experience using similar functions in WinPE to get model infos from HP WMI bios extensions.
      If you play around with this, please post your fixes/improvements/changes back to this thread.
      Have fun!
      Edit: Updated to show "Unable to query temperature" if the WMI query returns a null
      #AutoIt3Wrapper_Icon=temperature-2-multi-size.ico #RequireAdmin #NoTrayIcon #include <Temperature.au3> #include <FFLabels.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Global $winLogger = "C:\WINDOWS\system32\eventcreate.exe" ; application to use for event logging Global $MessageSource = "HP TempMon" Global $width = 185 Global $height = 15 Global $FontSize = 8 Global $FontFamily = 'Microsoft Sans Serif' Global $normClr = 0xFF000000 Global $warnClr = 0xFF0000 Global $WMInameSpace = "HP_BIOSNumericSensor" ;~ Global $WMIReadSensor = "Chassis Thermal Index" ; HP 8300 Global $WMIReadSensor = "System Ambient Temperature" ; HP Z400 Global $warnTmp = 46.1111111111 ; degrees C ;~ Global $warnTmp = 24 ; degrees C for testing Global $ovrtmpTime = 5 ; in minutes Global $hGUI = GUICreate("Board Temperature", $width, $height, -1, 0, BitOR($WS_SYSMENU, $WS_POPUP), BitOR($WS_EX_TOPMOST, $WS_EX_WINDOWEDGE, $WS_EX_TOOLWINDOW)) Global $lb1 = _GUICtrlFFLabel_Create($hGUI, "", -1, -1, $width, $height, 8, -1, 0, 1, $normClr) GUISetState(@SW_SHOW) _TempSenseLoop() Func _TempSenseLoop() While 1 Sleep(250) $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($hGUI) Return Case Else $s_ambTempC = _doQueryHPNumSens(5, $WMInameSpace, $WMIReadSensor, "100") ; + 100 Select Case $s_ambTempC = "" _GUICtrlFFLabel_SetTextColor($lb1, $normClr) _GUICtrlFFLabel_SetData($lb1, "Unable to query temperature") Case $s_ambTempC < $warnTmp _GUICtrlFFLabel_SetTextColor($lb1, $normClr) _GUICtrlFFLabel_SetData($lb1, "Board temperature: " & $s_ambTempC & "C" & "/" & Round(_CelsiusToFahrenheit($s_ambTempC), 0) & "F") Case Else _GUICtrlFFLabel_SetTextColor($lb1, $warnClr) _GUICtrlFFLabel_SetData($lb1, "Board temperature: " & $s_ambTempC & "C" & "/" & Round(_CelsiusToFahrenheit($s_ambTempC), 0) & "F") _doLog("System ambient temperature has exceeded " & $warnTmp & "C.", "WARN", $MessageSource) _AlarmMonitor($ovrtmpTime, $warnTmp) EndSelect EndSwitch WEnd EndFunc ;==>_TempSenseLoop Func _AlarmMonitor($min, $tmp) Local $Shutdown = 1 Local $s_ambTemp Local $freq = 3500 Local $shrt = 200 Local $long = 500 Local $timer = TimerInit() Local $wait = 1000 * ($min * 60) While TimerDiff($timer) < $wait $s_ambTemp = _doQueryHPNumSens(5, $WMInameSpace, $WMIReadSensor, "100") ; + 100 If $s_ambTemp >= $tmp Then _GUICtrlFFLabel_SetData($lb1, "WARNING! OVERTEMP!") Beep($freq, $shrt) Beep($freq, $shrt) Beep($freq, $shrt) Sleep(250) Beep($freq, $long) Beep($freq, $long) Beep($freq, $long) Sleep(250) Beep($freq, $shrt) Beep($freq, $shrt) Beep($freq, $shrt) _GUICtrlFFLabel_SetData($lb1, "Board temperature: " & $s_ambTemp & "C" & "/" & _CelsiusToFahrenheit($s_ambTemp) & "F") Sleep(2000) ElseIf $s_ambTemp < $tmp Then $Shutdown = 0 ExitLoop EndIf WEnd If $Shutdown = 1 Then _doLog("The system is shutting down due to overtemperature conditions. The temperature at the time of shutdown was " & $s_ambTemp & "C" & "/" & _CelsiusToFahrenheit($s_ambTemp) & "F", "ERRR", $MessageSource) Shutdown(24) ElseIf $Shutdown = 0 Then _doLog("System ambient temperature has been reduced below " & $warnTmp & "C.", "INFO", $MessageSource) EndIf EndFunc ;==>_AlarmMonitor Func _doQueryHPNumSens($min, $class, $name, $sleep) ; $sleep = milliseconds Local $s_tempReading = "" Local $s_HPBiosWMIService = "winmgmts:\\" & @ComputerName & "\Root\HP\InstrumentedBIOS" Local $objHPBiosWMIService = ObjGet($s_HPBiosWMIService) Local $s_biosQuery = $objHPBiosWMIService.ExecQuery("select * from " & $class & " where Name='" & $name & "'") $timer = TimerInit() ; we may need to wait for the HP WMI extensions to enumerate in WMI, in WinPE this takes some minutes. $wait = 1000 * ($min * 60) If Not IsObj($s_biosQuery) Then ; we do need to wait, put up a splash screen $s_WMISplash = 1 SplashTextOn("WMI", "Probing WMI (up to 10 minutes)...", 300, 50, -1, -1, 1) Sleep(1000) While TimerDiff($timer) < $wait Sleep(500) $objHPBiosWMIService = ObjGet($s_HPBiosWMIService) If IsObj($objHPBiosWMIService) Then Sleep(500) $colProdName = $s_biosQuery If IsObj($colProdName) Then For $oItem In $colProdName $s_tempReading = $oItem.CurrentReading Next EndIf EndIf WEnd ElseIf IsObj($s_biosQuery) Then ConsoleWrite($s_biosQuery & @CRLF) For $oItem In $s_biosQuery $s_tempReading = $oItem.CurrentReading Next Else $s_tempReading = 0 EndIf Sleep($sleep) Return $s_tempReading EndFunc ;==>_doQueryHPNumSens Func _doLog($message, $type, $msgsrc) Select Case $type = "INFO" $logTyp = "INFORMATION" Case $type = "WARN" $logTyp = "WARNING" Case $type = "ERRR" $logTyp = "ERROR" EndSelect If FileExists($winLogger) Then RunWait(@ComSpec & " /c " & $winLogger & " /L Application /T " & $logTyp & " /SO " & Chr(34) & $msgsrc & Chr(34) & " /ID 1000 /D " & Chr(34) & $message & Chr(34), "", @SW_HIDE) EndIf EndFunc ;==>_doLog  
       
       
      Temperature.au3
      FFLabels.au3
      HP_Temp.au3
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd