Jump to content
Sign in to follow this  

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 by spudw2k

Share this post


Link to post
Share on other sites

$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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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

$oExcel.Visible = True

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

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
Sign in to follow this  

  • Similar Content

    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
    • By _leo_
      Hey there! 😃
      I am having a problem with the _Excel_RangeFind. I am trying to search for a value in a particular cell range. The script copies the value from the internet. Copying and saving as a variable is working fine, but as soon as it should find the value in excel, nothing happens. ( I am not getting an error)
      #include <Excel.au3> Func Excel() Send("{CTRLDOWN}") Send("{c}") Send("{CTRLUP}") Local $sName = ClipGet() ;Text Local $sShortName = StringTrimRight ( $sName, 1) ;delete one letter Local $bOpenWorkBook = False, $oExcel = _Excel_Open() Local $sFilePath = "C:\Users\Acer\OneDrive\xyz.xlsx" Local $oWorkbook $oWorkbook = _Excel_BookAttach($sFilePath) If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath) $bOpenWorkBook = True EndIf sleep(15000) Send("{LWINDown}") Send ("{up}") ;maximize window Send("{LWINup}") sleep(1000) _Excel_RangeFind ($oWorkbook, $sShortName, "A3:A56") EndFunc Is anyone familiar with this problem or am I just missing some basic stuff? 
      Thanks for help!
    • By _leo_
      Hey guys
      I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. 
      This is what I have got so far:
       
      Local $oExcel_1 = _Excel_Open()
      Local $var = "C:\Users\Acer\xy"
      Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var)
       
      Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address)
       
      Whatever I try, I either get an error or it only reads "0".
       
      Thank you very much for any helpful thoughts!
×
×
  • Create New...