Search network for files and export to excel

Author Message
treppel

  • Total Posts : 2
  • Scores: 0
  • Reward points : 0
  • Joined: 10/15/2008
  • Status: offline
Search network for files and export to excel Wednesday, October 15, 2008 8:46 AM (permalink)
0
I am trying to create an array(list of machine names) and use this array to perform a recursive scan on all machines c: drives in the network.
I want to scan for exe, iso ...
I want to export each file it finds to an excel spread sheet.
 
So far I have the array working and the spread sheet working. I can do a export to csv, but everything is in one cell and the array overwrites the entry before it.
 
I would like separate cells for machine name, filename, path, and file size.
 
Please help.
 
Thanks,
TREP
 
 
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = "Computer"
$Sheet.Cells.Item(1,2) = "File_Name"
$Sheet.Cells.Item(1,3) = "Directory"
$Sheet.Cells.Item(1,4) = "File_Size"
$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True
 
$intRow = 2

Foreach($strComputer in get-content C:\PS_Scripts\servers.txt)
{
$Sheet.Cells.Item($intRow,1) = $strComputer
$colItems = $strComputer | %{gci \\$_\c$\ -include *.exe -recurse}
foreach ($objItem in $colItems) {
 $Sheet.Cells.Item($intRow,1) = $strComputer
    $Sheet.Cells.Item($intRow,2) = $_.name
    $Sheet.Cells.Item($intRow,3) = $_.path
    $Sheet.Cells.Item($intRow,4) = $_.length
 
$intRow = $intRow + 1
}
}
$WorkBook.EntireColumn.AutoFit()
Clear
 
#1
    ebgreen

    • Total Posts : 8227
    • Scores: 98
    • Reward points : 0
    • Joined: 7/12/2005
    • Status: online
    RE: Search network for files and export to excel Thursday, October 16, 2008 9:07 AM (permalink)
    0
    I'm confused. What exactly does the ouput from the script look like right now, and how do you want it to look different?
    "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
    Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
    http://www.visualbasicscript.com/m_47117/tm.htm
     
    #2
      treppel

      • Total Posts : 2
      • Scores: 0
      • Reward points : 0
      • Joined: 10/15/2008
      • Status: offline
      RE: Search network for files and export to excel Friday, October 17, 2008 12:20 AM (permalink)
      0
      it builds the spread sheet and lists the machine for every instance of the file it finds, but it does not show the file name, size, path, properties...

      I would like to populate the spreadsheet with all this information
      $Excel = New-Object -Com Excel.Application
      $Excel.visible = $True
      $Excel = $Excel.Workbooks.Add()
      $Sheet = $Excel.WorkSheets.Item(1)
      $Sheet.Cells.Item(1,1) = "Computer"
      $Sheet.Cells.Item(1,2) = "File_Name"
      $Sheet.Cells.Item(1,3) = "Directory"
      $Sheet.Cells.Item(1,4) = "File_Size"
      $WorkBook = $Sheet.UsedRange
      $WorkBook.Interior.ColorIndex = 8
      $WorkBook.Font.ColorIndex = 11
      $WorkBook.Font.Bold = $True

      $intRow = 2

      Foreach($strComputer in get-content C:\PS_Scripts\servers.txt)
      {
      $Sheet.Cells.Item($intRow,1) = $strComputer
      $colItems = $strComputer | %{gci \\$_\c$\ -include *.exe -recurse}
      foreach ($objItem in $colItems) {
      $Sheet.Cells.Item($intRow,1) = $strComputer
         $Sheet.Cells.Item($intRow,2) = $objfile.name
         $Sheet.Cells.Item($intRow,3) = $objfile.path
         $Sheet.Cells.Item($intRow,4) = $objfile.length

      $intRow = $intRow + 1
      }
      }
      $WorkBook.EntireColumn.AutoFit()
      Clear
      <message edited by treppel on Friday, October 17, 2008 12:23 AM>
       
      #3
        ebgreen

        • Total Posts : 8227
        • Scores: 98
        • Reward points : 0
        • Joined: 7/12/2005
        • Status: online
        RE: Search network for files and export to excel Monday, October 20, 2008 2:44 AM (permalink)
        0
        foreach ($objItem in $colItems) {
        $Sheet.Cells.Item($intRow,1) = $strComputer
          $Sheet.Cells.Item($intRow,2) = $objfile.name
          $Sheet.Cells.Item($intRow,3) = $objfile.path
          $Sheet.Cells.Item($intRow,4) = $objfile.length

        $intRow = $intRow + 1
        }


        Do you see the problem?
        "... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
        Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
        http://www.visualbasicscript.com/m_47117/tm.htm
         
        #4

          Online Bookmarks Sharing: Share/Bookmark

          Jump to:

          Current active users

          There are 0 members and 1 guests.

          Icon Legend and Permission

          • New Messages
          • No New Messages
          • Hot Topic w/ New Messages
          • Hot Topic w/o New Messages
          • Locked w/ New Messages
          • Locked w/o New Messages
          • Read Message
          • Post New Thread
          • Reply to message
          • Post New Poll
          • Submit Vote
          • Post reward post
          • Delete my own posts
          • Delete my own threads
          • Rate post

          2000-2012 ASPPlayground.NET Forum Version 3.9