locked
Extracting sub folder names and placing them in excel under Sub Directory Folder Name RRS feed

  • Question

  • Hey everyone, hope you are having a good day. I am trying to figure out the best way to pull the sub folders names and place them in the correct excel sheet under "Sub Directory Folder Name" cell. I appreciate any help you can offer is greatly appreciated it. below is my code. 

    $greaterthan30 = gci |select-object -expandProperty name | Where-Object { $_.length -gt 30 }
    $greaterthan30 = $greaterthan30.substring(0, 30)
    $lessthan30 = gci |select-object -expandProperty name | Where-Object { $_.length -lt 30 }
    $justright = $greaterthan30 + $lessthan30
    [array]::Reverse($justright)

    $excel = new-object -comobject Excel.Application
    $excel.visible = $true
    $WorkBook = $Excel.Workbooks.Add()
    $justright | ForEach-Object {
     $newsheet = $workbook.worksheets.add()
     $newsheet.name = $_ 
     $row = 1 
     $Column = 1 
    $newsheet.Cells.Item($row,$column)= 'Awesome Folder Name'
     $row= 2
     $column= 1 
    $newsheet.cells.Item($row,$column)= $_
    $row= 1
     $column= 2 
    $newsheet.Cells.Item($row,$column)= 'Sub Directory Folder Name'
    $usedRange = $newsheet.UsedRange
    $usedRange.EntireColumn.AutoFit() | Out-Null
     }


    • Edited by cmoneyg Tuesday, June 12, 2018 8:56 PM
    • Moved by Bill_Stewart Monday, July 30, 2018 1:48 PM This is not "scripts on demand"
    Tuesday, June 12, 2018 8:54 PM