Powershell script to edit and ‘save as’ an excel XLSM file

I have about 20 users offsite that need to fill out an excel log for their work each day. To help them we make available a 'read only' blank template, and I have setup a desktop icon for a powershell script to prepare the log each morning for them. My script opens the read only template, edits to fill in some basic information, then does a ‘save as’ to deposit the log in their documents with the correct name, and opens the file for them.

This script has worked flawlessly for years, but a week ago we made a change, we changed the log file to a macro enabled (XLSM) excel workbook.

To facilitate this change, I opened the old XLSX and did a saveas macro-enabled workbook, to make sure the file was internally formatted correctly. I also edited my script changing the extensions from .xlsx to .xlxm. And I remotely tested everything on a couple of users computers. Everything worked fine.

But of the 20 users, one had issues, the script would not save the file, the powershell errors showed a filetype error on the ‘save as’. It had issues with that file type. The other 19 or so users did not have this issue. Doing some looking, I found recommendations to use an ‘add-type’ and an assembly to specify the filetype. I added the following lines of code as recommended.

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
...
$workbook.saveas($filename,$xlFixedFormat)

These changes did fix the issue on that one users computer. But it caused new issues on one other computer. The new problem computer was getting the following errors when I run the script.

PS C:\Users\MyUser\OneDrive\Setup> .\DailyCopy1.ps1
Add-Type : Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.
At C:\Users\MyUser\OneDrive\Setup\DailyCopy1.ps1:2 char:1
+ Add-Type -AssemblyName Microsoft.Office.Interop.Excel
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.AddTypeCommand

Unable to get the SaveAs property of the Workbook class
At C:\Users\MyUser\OneDrive\Setup\DailyCopy1.ps1:45 char:10
+          throw $_.Exception.Message
+          ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Unable to get t... Workbook class:String) [], RuntimeException
    + FullyQualifiedErrorId : Unable to get the SaveAs property of the Workbook class

PS C:\Users\MyUser\OneDrive\Setup>

To deal with the first error, I tried using another Add-Type command and specify the path to the DLL directly.  I also verified this dll does exist at this path on the PC.

Add-Type -Path $env:WINDIR\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

This second command runs successfully when I launch the script on the problem computer, but then the ‘save as’ later in the script still fails.

The funny thing is, it also kind of works. When I check the users document folder, the file is created with the correct name, and the edits are in place. But the script doesn’t continue and the file is never launched for the user. Also the excel process for the edit stays in system memory.

Any ideas why this isn’t working correctly on some computers?

Some info about the computer. All of the users are on the same windows and office software.

OS = Win 10 64bit with current updates.
Office = Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20108) 32-bit

Things I have tried = sfc /scannow 

Online repair of office.

.NET Framework Repair Tool

Several reboots…

Testing on other computers, it works on other computers.

Here is the full script as it stands now, with the above edits =

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
Add-Type -Path $env:WINDIR\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbookMacroEnabled
$EMSDate = Get-Date -Format "yyyy-MM-dd"
$filecheck = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsm"

# Check if template is XLSM or not
if (!(Test-Path $filecheck)) {
   $filename = "$env:UserProfile\OneDrive\WorkingFolder\" + $EMSDate + " Daily Forms.xlsx"
   $template = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsx"
   }
else {
   $filename = "$env:UserProfile\OneDrive\WorkingFolder\" + $EMSDate + " Daily Forms.xlsm"
   $template = "$env:UserProfile\OneDrive\Vessel Templates\EMSV5.xlsm"
   }
$EMSEmpList = "$env:UserProfile\OneDrive\Vessel Templates\Data\EMSEmpList.csv"
$EMSStrapping = "$env:UserProfile\OneDrive\Vessel Templates\Data\StrappingTables.csv"
$EMSBoatName = $env:emslocaluser
# If the file does not exist, create it.
if (-not(Test-Path -Path $filename -PathType Leaf)) {
     try {
        $excel = new-object -comobject Excel.Application
        $excel.DisplayAlerts = $false
        $excel.ScreenUpdating = $false
        $excel.Visible = $false
        $excel.UserControl = $false
        $excel.Interactive = $false
        $workbook = $excel.workbooks.add($template)
        $s1 = $workbook.sheets | Where-Object {$_.name -eq 'TABLES'}
        $s1.range("A2:A2").cells = $EMSDate
        $s1.range("A3:A3").cells = $EMSBoatName
        $s1.range("A5:A5").cells = $EMSEmpList
        $s1.range("A6:A6").cells = $EMSStrapping
        $workbook.saveas($filename,$xlFixedFormat)
        $excel.quit()
        Start-Sleep -Seconds 1
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
        Start-Sleep -Seconds 2
        Remove-Variable excel
        Start-Sleep -Seconds 2
        Invoke-Item $filename
     }
     catch {
         throw $_.Exception.Message
     }
 }
# If the file already exists, show the message and do nothing.
 else {
    $ButtonType = [System.Windows.Forms.MessageBoxButtons]::OK
    $MessageIcon = [System.Windows.Forms.MessageBoxIcon]::Error
    $MessageBody = "Cannot create new Daily Log because one already exists in the working folder with todays date."
    $MessageTitle = "Duplicate"
    [System.Windows.Forms.MessageBox]::Show($MessageBody,$MessageTitle,$ButtonType,$MessageIcon)
 }