Excel

[hemichallenger]

import-csv Computer List.csv | 

foreach {
    $system = "" | 
    select Name, UserName, MacAddress, IPAddress

    $colItems = Get-WmiObject -Class Win32_NetworkAdapterConfiguration `
    -ComputerName $_.Computer -Filter "IpEnabled = TRUE"
    ForEach ($objItem in $colItems){
    $System.MacAddress = $objItem.MacAddress
    $System.IPAddress = $objItem.IpAddress[0]}

    $server = Get-WmiObject -Class Win32_ComputerSystem `
    -ComputerName $_.Computer

    $System.Name = $server.Name
    $system.UserName = $server.Username
    $system
   } |
Export-Csv c:\Info.csv -NoType

Can this be done in autoit?

[jguinch]

yes

[spudw2k]

Sure can, not sure what this has to do with Excel though.

edit: Granted it takes more lines of code, but can easily be scripted.
Search for examples using WMI and how to read a text file.

edit: Oh, I get it....CSV file, opened with Excel.  Makes sense, I just don't automatically associate CSVs with Excel.

Edited August 25, 2014 by spudw2k

[JLogan3o13]

Try AutoIt Scriptomatic for the WMI code, get that in place and the post it so we can assist you with importing into Excel.

 

'?do=embed' frameborder='0' data-embedContent>>

[hemichallenger]

$wbemFlagReturnImmediately = 0x10
$wbemFlagForwardOnly = 0x20
$colItems = ""
$strComputer = "localhost"

$Output=""
$Output = $Output & "Computer: " & $strComputer  & @CRLF
$Output = $Output & "==========================================" & @CRLF
$objWMIService = ObjGet("winmgmts:\\" & $strComputer & "\root\CIMV2")
$colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE (IPEnabled = True)", "WQL", _
                                          $wbemFlagReturnImmediately + $wbemFlagForwardOnly)
$colSettings = $objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem", "WQL", _
                                          $wbemFlagReturnImmediately + $wbemFlagForwardOnly)

If IsObj($colItems) then
   For $objItem In $colItems
       Next
If IsObj($colSettings) then
   For $objItem In $colSettings
            $Output = $Output & "IPAddress: " & $objItem.IPAddress(0) & @CRLF
            $Output = $Output & "MACAddress: " & $objItem.MACAddress & @CRLF
            $Output = $Output & "UserName: " & $objItem.UserName & @CRLF
            $Output = $Output & "Name: " & $objItem.Name & @CRLF
      if Msgbox(1,"WMI Output",$Output) = 2 then ExitLoop
      $Output=""
   Next
Else
   Msgbox(0,"WMI Output","No WMI Objects Found for class: " & "Win32_NetworkAdapterConfiguration" )
Endif
EndIf

Okay got the scriptomatic installed. So how would I import a csv file then export the results into a csv file?

[JLogan3o13]

Can you please explain what is in the csv you want to "import"? An example of what information is in the csv, and how you want to manipulate it in your script, would be helpful. If you cannot share the actual file, perhaps upload a reproducer csv.

[hemichallenger]

import CSV I would have a list of computer names in column A (computer list). after running it would export all the information in to a file named info.csv. Had to change them to XLSX files. CSV files aren't permitted to upload.

Computers List.xlsx

Info.xlsx

[kylomas]

The Excel portion can be done like this...

#include <Excel.au3>
#include <array.au3>

; get source spreadsheet and read it to 1D arry


local  $sWorkbook = @ScriptDir & "\computers list.xlsx"
local  $oExcel = _Excel_Open(False) ; Start Excel or connect to a running instance
local  $oBook = _Excel_BookOpen($oExcel, $sWorkbook, True) ; Open the workbook
local  $aData = _Excel_RangeRead($oBook, Default, $obook.ActiveSheet.Usedrange) ; Read all used cells into an array

; iterate array values through WMI to get your values...I'll just fake some data and write it to Excel

local $aFinal[ubound($aData)][3]

$aFinal[0][0] = 'Computer'
$aFinal[0][1] = 'UserName'
$aFinal[0][2] = 'Ipaddress'

for $1 = 1 to ubound($aData) - 1
    $aFinal[$1][0] = $aData[$1]
    for $2 = 1 to ubound($aFinal,2) - 1
        $aFinal[$1][$2] = $1 & ' - ' & $2
    Next
Next

; now open a new spreadsheet, write the 2D array starting at cell B1 and save it as info.xlsx (overwritting it if it exists)

Local $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFinal, "B1")
_Excel_BookSaveAs($oWorkbook, @scriptdir & '\info.xlsx',default,true)
_Excel_Close($oExcel, False) ; Close Excel (and the workbook)

; display the spreadsheet

shellexecute(@scriptdir & '\info.xlsx')

* - This is right out of the Help file minus the error checking.

kylomas

[water]

Why close Excel and use SHellExecute to display tghe WorlBook?  Just make Excel visible again

$oExcel.Visible = True

[kylomas]

Duh!!  Thanks water...

[water]

[hemichallenger]

That works great. But im not a expert yet so still don't know how I would import the excel into a WMI.

[kylomas]

You don't import the Excel to the WMI.

Logical flow:

read spreadsheet to array (code present)

execute WMI instruction for each PC in the array (for you to write)

save to new spreadsheet (code present)