Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need Help with Powershell Script

Posted on 2014-01-31
7
Medium Priority
?
414 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 2000 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. 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 71

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 71

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

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

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

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

721 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