Jump to content
Sign in to follow this  
hemichallenger

Excel

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 by spudw2k

Share this post


Link to post
Share on other sites
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>>


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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.


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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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


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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
kylomas

Duh!!  Thanks water...


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
water

;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
hemichallenger

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

Share this post


Link to post
Share on other sites
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)


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

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×