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

Pester
1

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

2

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.