Solved

Need Help with Powershell Script

Posted on 2014-09-23
4
539 Views
Last Modified: 2014-09-23
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
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
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Dan McFadden
ID: 40339234
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
 

Author Comment

by:amstoots
ID: 40339391
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
 
LVL 40

Accepted Solution

by:
footech earned 500 total points
ID: 40339677
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
 

Author Closing Comment

by:amstoots
ID: 40340633
footech - you hit it dead on... the string correction on Line 36 worked perfect..... thank you
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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