Use Excel Template with Get-Content of DIR Listing to Save as Each Report Name

Welcome Forums Pester Use Excel Template with Get-Content of DIR Listing to Save as Each Report Name

Viewing 1 reply thread
  • Author
    Posts
    • #111386
      Participant
      Topics: 2
      Replies: 1
      Points: 0
      Rank: Member

      Hi All,

      I am attempting to automate the creation of Excel reports based on an pre-defined Excel Template. I have managed to get a list of directory names using the Get-ChildItem cmdlet, which has been exported into an excel spreadsheet.

      What I wish to do is open the Excel Template, get the names from the workbook containing the direcotry listing, and save an Excel report or each directory, using the directory name.

      The PowerShell Script I have patched together so far is as per below. The part that I am experiencing issues with is the foreach section. As I am entirely new to PowerShell, I am not enrirely sure how to amend the foreach section to reference the list of directory names in the AreaName-10SEP18-RootList.xls spreadsheet and then save an .xlsx report spreadsheet for each directory shown, using the directory name.

      If I could get some pointers in the right direction, it would be very much appreciated.

      ##########################################
      ########## Excel Template Save As Script ##########
      #########################################

      $xl = New-Object -ComObject Excel.Application

      $xl.visible = $true
      $xl.DisplayAlerts = $false

      ############### Open Excel Template ###############

      #Open the Excel Template
      $xl.workbooks.open(“\\FileServerName\ShareName\FolderContainingExcelTemplate\02-Template – Network_DIR_File_&_NTFS_Analysis.xltx“)

      #ForEach DIR shown in CSV, save workbook template in the specified directory path

      $RootList = Get-Content\\FileServerName\ShareName\FolderContainingExcelTemplate\AreaName\AreaName-10SEP18-RootList.xls

      —————————————————————————————————–

      ################################################################
      ################### ERROR OCCURS AT THIS POINT ####################
      ################################################################
      foreach ($file in $RootList) {
      $xl.workbooks.SaveAs(“\\FileServerName\ShareName\FolderContainingExcelTemplate\AreaName\$file.xlsx”)
      }

      ############# Clean up the Environment ############

      $xl.workbooks.close()
      $xl.quit()

      #Check and you will see an xl process still exisiting after quiting
      Remove-ComOjbject -Verbose
      Start-Sleep -Milliseconds 250
      Get-Process Excel

      #Now to remove the $xl com object to ready it for garbage collection
      [System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)

      ————————————————————————————————–

      Cheers,

      jcambeul

    • #112024
      Senior Moderator
      Topics: 9
      Replies: 1265
      Points: 4,582
      Helping Hand
      Rank: Community Hero

      Welcome to PowerShell.org, Please post you query in General PowerShell Q&A as this forum is for Pester.

      and, I request you to format the code in the forum which makes other to easily understand your code, below link will help you.

Viewing 1 reply thread
  • The topic ‘Use Excel Template with Get-Content of DIR Listing to Save as Each Report Name’ is closed to new replies.