Solved

Need Help with Powershell Script

Posted on 2014-01-31
7
411 Views
Last Modified: 2014-02-01
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
0
Comment
Question by:amstoots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39825032
How do you want the script to decide Region 1, 2, 3 etc?
0
 

Author Comment

by:amstoots
ID: 39825059
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
 
LVL 19

Accepted Solution

by:
Raheman M. Abdul earned 500 total points
ID: 39825229
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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

Author Closing Comment

by:amstoots
ID: 39826367
The script works perfect ... thank you for help !!!!!!
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39826411
It shouldn't. $xlShiftToRight = -4161  is set only after it has been already used. It should not work for the first loop go.
0
 

Author Comment

by:amstoots
ID: 39826729
What do you mean .... Qlemo.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39826978
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question