Automation

I know it’s Friday, but I’m changing it a bit up today. I just created this little script that I think could be helpful to others. This one isn’t for the masses, but I have a feeling it will help out more than a few people, and trigger some more ideas for script ideas.

# Specify the path to the Excel file
$excelFilePath = "C:\Scripts\WorksheetTitle.xlsx"
$outputCsvPath = "C:\Scripts\exportworksheetlabels.csv"

if (!(Test-Path $ExcelFilePath)) {
    Write-Error "Excel file not found at the specified path: $ExcelFilePath"
    return
}

# Open the Excel file
$excel = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList (Get-Item $excelFilePath)

# Get the worksheet names
$worksheetNames = $excel.Workbook.Worksheets | ForEach-Object { $_.Name }

# Add quotes around each worksheet name and convert them to a comma-separated string
$commaSeparatedWorksheetNames = ($worksheetNames | ForEach-Object { '"' + $_ + '"' }) -join ','

# Export the worksheet names to a CSV file
$commaSeparatedWorksheetNames | Set-Content -Path $outputCsvPath

# Dispose the ExcelPackage object to release resources
$excel.Dispose()

What this script does, is takes all of your worksheet names in an excel sheet, and exports them with quotes and commas as if they were an array. An example would be “Dog”, “Cat”, “Cheetah”, where Dog, Cat, and Cheetah were the 3 different worksheet names. Don’t ask how I picked those names, but I’d be curious to see who has those worksheet names in an excel workbook!

Now you are probably wondering what the use case for this is, and for me, which I found very useful was we have an excel workbook that is separated by job title. And inside each of these worksheets are the 365 Distribution/Security Lists and 365 Groups for that title. This makes it so we have one “database” of lists, and only need to make the change once, if a standard changes. This works for both adding and removing them. But, the user who is adding/removing these groups for that user, needs to know the exact title for that user and how it is spelled on the worksheet…

This is why we export the titles this way, so then we can copy and paste them into a validateset within the function for the script, so the user has tab completion and so the script won’t even start without having the correct title.

Hope this helped you out and/or gave you some more ideas, and if you see anyway it can be improved, I’m all about being more efficient. Let me know if you would want to see the script to add users by title from an excel worksheet. Have a great Friday!

Github – Copy-ExcelWorksheetName