Search code examples
excelvbapowershellwindows-services

Error when Running an Excel Macro from a Windows Service Application

I have created a Windows Service Application that does many things but a part of it is producing an error. My service runs a PowerShell script that opens an Excel document (I know Microsoft does not support this, but opening the Excel doc is actually not whats giving me an issue). I get the error below when I try to run a macro on it and I have no idea how to fix it.

Exception calling "Run" with "1" argument(s): "'C:\WINDOWS\system32\PERSONAL.XLSB' could not be found. 
Check the spelling of the file name, and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files,
make sure that the file has not been renamed, moved, or deleted."

At line:54 char:1
+ $excel.Run("PERSONAL.XLSB!MacroForSoftwareFeatureLicensesXLS")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have applied other solutions already like adding a 'Desktop' folder to C:\Windows\System32\config\systemprofile and C:\Windows\SysWOW64\config\systemprofile. This worked for allowing me to open the excel doc.

I have tried putting the PERSONAL.XLSB file in the C:\WINDOWS\system32 folder but this does nothing. Similarly, I also tried putting the PERSONAL.XLSB file in C:\WINDOWS\sysWOW64 but this completely halts my service.

My PowerShell script is the following:

$myLocalXLS = "C:\Users\aUser\Desktop\aFolder\myLocalXLS.xls"
$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts = $FALSE
$myXLSWorkbook = $excel.Workbooks.Open($mylocalXLS)
$excel.Run("PERSONAL.XLSB!MacroForMyLocalXLS")
$myXLSWorkbook.Save()
$excel.DisplayAlerts = $TRUE
$excel.Quit()
Solution

  • I think you actually have to open the PERSONAL.XLSB manually, e.g. something like this worked for me:

    $myLocalXLS = "C:\Users\aUser\Desktop\aFolder\myLocalXLS.xls"
$myPersonalXLSB = "C:\Users\aUser\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB"
$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts = $FALSE
$myXLSWorkbook = $excel.Workbooks.Open($mylocalXLS)
$myPersonalWorkbook = $excel.Workbooks.Open($myPersonalXLSB)
$excel.Run("PERSONAL.XLSB!MacroForMyLocalXLS")