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

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

2

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,

Which is the best available video course for beginners.

3

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.