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

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

 
Participant
2 months, 3 weeks ago.

  • Author
    Posts
  • #111386

    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 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

    Participant
    Points: 861
    Helping Hand
    Rank: Major Contributor

    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.

You must be logged in to reply to this topic.