CODEWITHSUNDEEP
arrayspowershell
TelluRye
TelluRye

Reputation: 63

Powershell advance through an array in a ForEach loop (or nested Foreach)

Folks,

I found a few answers, but they seem to be for manual arrays, or when you know the definite number. I'll have an array that I dont know how many will be in there. Was wondering if I could get help.

Basically - I want it to go thru each server, get the OCX and DLL name, version, and date, and put it in a spreadsheet.

It seems to work fine...only once. It doesnt go on to the next file in my array.

Im so close, can someone help me tell it (OK, dont advance to the next server until you are done getting ALL the files)

Here's my code so far. Works great - but only for one file per server.

Many thanks in advance.

$Servers = Get-Content C:\temp\Servers.txt

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=-1
$WorkBook=$objExcel.WorkBooks.Add()
$sheet=$workbook.worksheets.item(1)
$class = "win32_Share"

$sheet.Cells.item(1,1)=("Server")
$sheet.Cells.item(1,2)=("File Name")
$sheet.Cells.Item(1,3)=("File Version")
$sheet.Cells.Item(1,4)=("Date")
$sheet.Range("A1:D1").interior.colorindex = 43  
$x=2


$i = 0

foreach ($Server in $Servers) {


    $infos = Invoke-Command -computername $Server {Get-ChildItem "V:\Service\*" -File -Include *.dll, *.ocx | Select-Object name,@{label="FileVersion";expression={$_.versioninfo.fileversion}},@{label="Date";expression={$_.LastWriteTime}}}

    $info = $infos[$i]



$sheet.Cells.item($x, 1)=($Server)
$sheet.Cells.item($x, 2)=($info.name)
$sheet.Cells.item($x, 3)=($info.FileVersion)
$sheet.Cells.item($x, 4)=($info.Date)
$x++
$i++
}

#Autofit columns when completed
$range = $sheet.usedRange
$range.EntireColumn.Autofit()

It never seems to advance thru the $info[$i] array.

Upvotes: 2

Views: 578

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36342

What I would suggest is to get all of that info at once, sort it by server, select what you need, convert it to a tab delimited csv, copy it to the clipboard, and paste it to the spreadsheet.

$Servers = Get-Content C:\temp\Servers.txt

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=-1
$WorkBook=$objExcel.WorkBooks.Add()
$sheet=$workbook.worksheets.item(1)
$class = "win32_Share"
$infos = Invoke-Command -ComputerName $Servers -ScriptBlock { Get-ChildItem "V:\Service\*" -File -Include *.dll, *.ocx }
$infos | 
    Sort PSComputerName | 
    Select-Object  @{l='Name';e={$_.PSComputerName}},@{l='File Name';e={$_.Name}},@{label="File Version";expression={$_.versioninfo.fileversion}},@{label="Date";expression={$_.LastWriteTime}} |
    ConvertTo-CSV -Del "`t" |
    | Clip
$sheet.cells.item(1,1).PasteSpecial() | Out-Null

$sheet.Range("A1:D1").interior.colorindex = 43 
#Autofit columns when completed
$range = $sheet.usedRange
$range.EntireColumn.Autofit()

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32230

Just nest another foreach:

$x = 2
foreach ($Server in $Servers) {
    $infos = Invoke-Command -computername $Server { Get-ChildItem "V:\Service\*" -File -Include *.dll, *.ocx | Select-Object  name,@{label="FileVersion";expression={$_.versioninfo.fileversion}},@{label="Date";expression={$_.LastWriteTime}}}

    foreach ($info in $infos) {
        $sheet.Cells.item($x, 1)=($Server)
        $sheet.Cells.item($x, 2)=($info.name)
        $sheet.Cells.item($x, 3)=($info.FileVersion)
        $sheet.Cells.item($x, 4)=($info.Date)
        $x++
    }
}

I also recommend renaming $x to something more descriptive, like $Row.

Upvotes: 4

Related Questions