Need Help with Powershell Script

Greeting Experts,
      I need some help with a PowerShell script designed open a excel file, delete list of rows (until it hit a condition), and save it using the same file name.  To give a you a better idea, I receive several attachments once a week from our Antiviruses Management server with the states of daily infections. The excel files come in two parts. One is the list of malware, Trojans, and viruses names (along with no# of detections of each threat) and 2 is a Drill down detail list of each event. The propose of the script is to dynamically strip out the list of Threats until I get the First cell of the Drill down list in to cell “A1” (i.e. Event Category) so I don’t have to do it manually.
What I am looking to do is to expand on this script and insert two additional columns (one named “region” and the second named “date”) in front of the first column (i.e. “A1”). After it has completed removing the unnecessary rows the script currently does now…. Then put in the Region No# Folder its located in (i.e. Region1, Region2, or Region3), and put in current date in to the 2nd column in the following format (i.e. YYYY_mm_dd)


function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $false

$Lookupdir="C:\Region Reports\Region1"    
$days_backdate=$(Get-Date).AddDays(-1)    
 
$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)} | where { $_.Creationtime -gt $days_backdate }
foreach ($file in $files)
{
$objWorkbook = $objExcel.Workbooks.Open($file.Fullname)
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$i = 1
$cell=$objWorksheet.Cells.Item($i, 1)

Do {
    If ( $cell.value() -ne "Event Category"  )
      {
            $objRange = $cell.EntireRow
            $objRange.Delete()
            $i-=1
      }
      $i += 1
    $cell=$objWorksheet.Cells.Item($i, 1)
}
While ($cell.value() -ne "Event Category")

$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
$objExcel.Workbooks.Close()
$objExcel.Quit()
}

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel) 

Open in new window

ExampleSpreadsheet.xlsx
ExampleSpreadsheet2.xlsx
MikeSecurityAsked:
Who is Participating?
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Revised now:

Try this (this time complete code, tested on my side, working)
#-----------------------------------------------------------------------

function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

$objExcel = new-object -comobject excel.application
$objExcel.Visible = $false

$Lookupdir="C:\Region Reports\Region1"    
$days_backdate=$(Get-Date).AddDays(-1)    
 
$files = Get-ChildItem $Lookupdir -Recurse | where-object {!($_.psiscontainer)}  | where { $_.Creationtime -gt $days_backdate }
foreach ($file in $files)
{
$objWorkbook = $objExcel.Workbooks.Open($file.Fullname)
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$i = 1
$cell=$objWorksheet.Cells.Item($i, 1)

Do {
    If ( $cell.value() -ne "Event Category"  )
      {
            $objRange = $cell.EntireRow
            $objRange.Delete()
            $i-=1
      }
      $i += 1
    $cell=$objWorksheet.Cells.Item($i, 1)
}
While ($cell.value() -ne "Event Category")

$region=$file.FullName.Split("\")[-2]
$date = Get-Date -Format "yyyy_MM_dd"

$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)

$objRange = $objExcel.Range("A1").EntireColumn
[void] $objRange.Insert($xlShiftToRight)

$xlShiftToRight = -4161
$objWorksheet.Cells.Item(1,1) = "Region"
$objWorksheet.Cells.Item(1,2) = "Date"


$cell=$objWorksheet.Cells.Item($i+1, 3)

While ($cell.value() -ne $null)
 {
            $objWorksheet.Cells.Item($i+1,1)=$region;
            $objWorksheet.Cells.Item($i+1,2)=$date;
     $i += 1
    $cell=$objWorksheet.Cells.Item($i+1, 3)
}

$objExcel.displayalerts=$false
$objExcel.activeworkbook.saveas($file.fullname)
$objExcel.Workbooks.Close()
$objExcel.Quit()
}

Release-Ref($objRange)
Release-Ref($objWorksheet)
Release-Ref($objWorkbook)
Release-Ref($objExcel)
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
How do you want the script to decide Region 1, 2, 3 etc?
0
 
MikeSecurityAuthor Commented:
Based on the Network folder name the excel documents are located in ...

i.e..."\\IP Address\Network Share\Region1\"

i.e..."\\IP Address\Network Share\Region2\"

i.e..."\\IP Address\Network Share\Region3\"
0
WEBINAR: 10 Easy Ways to Lose a Password

Join us on June 27th at 8 am PDT to learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees. We'll cover the importance of multi-factor authentication and how these solutions can better protect your business!

 
MikeSecurityAuthor Commented:
The script works perfect ... thank you for help !!!!!!
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It shouldn't. $xlShiftToRight = -4161  is set only after it has been already used. It should not work for the first loop go.
0
 
MikeSecurityAuthor Commented:
What do you mean .... Qlemo.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Move
$xlShiftToRight = -4161
inside the loop before e.g.
$objExcel = new-object -comobject excel.application
to have it work reliably. As-is, when the loop is running the very first iteration,
[void] $objRange.Insert($xlShiftToRight)
is run twice without $xlShiftToRight having a value. Then the value is set, and in the next loop iteration all is fine.

But I have to correct myself. It still works, though the code is not correct, because the default for Range.Insert with a entire column selected is to move the existing columns to the right, which is the effect you need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.