none
select-object and foreach RRS feed

  • General discussion

  • Hello,

    I was wondering if anyone could offer any advice on this powershell script.

    I'm checking a directory for a few things:

    • to see if a specific file type exists and count it
    • to filter that file type with sort-object and select-object (if they exist)
    • then run the files (if they exist) through the loop to delete columns, add other columns, create the header and add content.
    • The content will be the date received and the filename

    I've done something like this before...but can't quite get this one to work. I ultimately need the filename to populate in B2 in a new column and then fill to the end of the used range.

    Thank you! This forum has helped me dramatically advance my skills and career!

    $counted_variable = get-childitem "filepath\*specific_file_to_look_for*.xlsx" | measure | % { $_.Count }
    
    $files_ = get-childitem "filepath\*.*" | sort-object | select-object -ExpandProperty basename
    
    $Excel = new-object -comobject excel.application 
    $Excel.visible = $True
    $Excel.DisplayAlerts = $False
    
    if($counted_variable -gt 1){
    
    foreach ($file in $files){
     
     $xlShiftToRight = -4161  
     $WorkBook =  $Excel.Workbooks.Open($file.FullName)
     $range = $WorkBook.activesheet.range("a1:b1").entirecolumn
     $range.Insert($xlShiftToRight)
    
     $range = $WorkBook.activesheet.range("m1:n1").entirecolumn
     $range.delete()
    
     $range_ = $WorkBook.activesheet.range("a1") = "email_rec_date"
     $range_a = $WorkBook.activesheet.range("b1") = "filename"
     $range_b = $WorkBook.activesheet.range("b2") = $files
       
    }}

    • Changed type Bill_Stewart Monday, March 12, 2018 7:16 PM
    • Moved by Bill_Stewart Monday, March 12, 2018 7:16 PM Unanswerable drive-by question
    Thursday, December 28, 2017 1:22 AM

All replies

  • Your question is kind of vague.  Are you asking how to insert rows into a sheet from a PS Object collection?  You have to assign cells by object property name.  There is also a method that can insert an array (n-dimensions) into a sheet.


    \_(ツ)_/

    Thursday, December 28, 2017 1:35 AM
  • Sorry that it's vague...I appreciate your help!

    I'm looking to populate a specific range on each file with the proper filename that is passed from the get-childitem array.

    To clarify more:

    get-childitem "filepath/*.xlsx" | sort-object | select-object -ExpandProperty basename

    returns file1,file2, etc (as needed)

    The filename string "File1" is populated in a cell range inside a used range (column b) in file1.

    The filename string "File2" is populated in a cell range inside a used range (column b) in file2.

    Thursday, December 28, 2017 1:42 AM
  • Still doesn't make sense.


    \_(ツ)_/

    Thursday, December 28, 2017 1:44 AM
  • What does any of this have to do with Excel?

    Tell what you want to do (what results do you want?), not how you think it needs to be done (put such-and such-data into Excel).

    FYI this is not an Excel forum. We can help you with the first part but Excel questions are for a different forum.


    -- Bill Stewart [Bill_Stewart]

    Thursday, December 28, 2017 3:59 PM