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-09-23
4
Medium Priority
?
565 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 41

Accepted Solution

by:
footech earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to count occurrences of each item in an array.
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…

722 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