Use Excel Template with Get-Content of DIR Listing to Save a Report for each DIR

Welcome Forums General PowerShell Q&A Use Excel Template with Get-Content of DIR Listing to Save a Report for each DIR

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #112036

    Participant
    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 for 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 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 AreaName-10SEP18-RootList.xls, 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

  • #112037

    Participant
    Points: 65
    Published
    Rank: Member

    What you need here is ImportExcel module. This is the best available module for managing excel in PowerShell.

    since you mentioned

    As I am entirely new to PowerShell

    I recommend you to take some time in learning PowerShell. Reading documentation will help you alot here. Please go through the help documentation for this module. You can start your PowerShell journey from,

    https://mva.microsoft.com/liveevents/powershell-jumpstart

    Which is the best available video course for beginners.

    • #112088

      Participant
      Points: 0
      Rank: Member

      Thanks for the response. I will take a look at the ImportExcel module. As it happens, I'm already going through the PowerShell Jumpstart on Microsoft MVA, so will continue with progress. I will be back with my progress. Thanks, cambeul.

You must be logged in to reply to this topic.