• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Need Help with Powershell Script

Greeting Experts,
I need some help with a PowerShell script. The purpose of the script is to delete list a of rows, and two columns, and populate” File Name” in column A then “Creation Date” in column B (script below). What I want  to do is instead of putting the creation date in to column B insert the modified date in to the Date column… can someone give me a hand with this issue.  
Column A: File Name: Region1 <No need to change>
Column B Date: Creation Date < Need to change it to the documents modified date>


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
$xlShiftToRight = -4161
$objExcel.Visible = $false 
$Lookupdir="U:\EPOReports\Region1\New folder\"    
$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)


$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) 

Open in new window

RawFile.xlsx
Final-Results.xlsx
0
Mike
Asked:
Mike
  • 2
1 Solution
 
Dan McFaddenSystems EngineerCommented:
The Last Modified info is referenced as:  LastWriteTime when viewed thru Get-ChildItem.

So just change this section of the script from this:

...   where { $_.Creationtime -gt $days_backdate }  ...

to

...  where { $_.LastWriteTime -gt $days_backdate }  ...

You can view all the file attributes by doing the following:
1. create an empty text file.  {example:  test.txt}
2. at PS console, run:  Get-ChildItem -Filter test.txt | select *

This will dump all the attributes for the test.txt file.  Next, add some text to the test.txt file and re-run the command in step #2.  You can see that the LastWriteTime has been updated.

Dan
0
 
MikeSecurityAuthor Commented:
You may have miss understood the description of my problem.  What I am trying to do is instead of using the creation date of the document. I want to use the modified date of the document and have the script input that information in to column B (i.e. Date)  in the form of YYYY_mm_dd…


 Example
0
 
footechCommented:
If I'm understanding correctly, you would just need to change line 36 to:
$date = Get-Date $file.LastWriteTime -Format "yyyy_MM_dd"

Open in new window

0
 
MikeSecurityAuthor Commented:
footech - you hit it dead on... the string correction on Line 36 worked perfect..... thank you
0

Featured Post

Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now