CODEWITHSUNDEEP
sql-serverexcelpowershelloffice365dcom
MilkyMan
MilkyMan

Reputation: 1

SQL Powershell Excel refresh automation suddenly stop working after upgrade to Office 365

We have curious issue with our automated Excel refresher process (50+ Excels daily) which were working smoothly for years and were able to sustain already 2 Office upgrades in past (from 2010 32 bit to 64 bit and then later to 2013 64 bit version). Recently we have been forced to migrate to new Office 365. There were our trouble started.

Excel files were smoothly refreshed with this automation and then suddenly after 8-24 hours process refuse to continue. We got general error message: You cannot call a method on a null-valued expression. Like something suddenly lost execution privileges to some object.

Error is not associated with Account (who runs it), not associated with file excel which got error first time. Once we got this error for first time NON of excel file will be refreshed any more. Unless we restart PC! Then everything is working fine again for another 8-24 hours! Issue is not associated with particular file, particular time, particular user or anything. Issue behavior is so volatile that we are not able clearly determine what is the cause of the issue, we are not able to find any pattern.

At last we found out one thing, not only reset of PC works to solve the issue, but also restart of process OfficeClickToRun.exe always helps. It is curious, i have tried to Disable this process for good, but then Excel wont start anymore, so i have been forced to enable it again. This process is a must for excel to work in Office 365.

Technology we are using for auto Excel refresh: SQL server 2014 job scheduler, executed PowerShell script with COM object Excel in it (see code below).

Also once the process is stuck even direct Powershell script execution does not work any more (till reset). Powershell refuse to open Excel comobject after this at all. Before error Powershell works just fine with same excel file. This might suggest that issue is not connected to remote execution from SQL (scheduled job), nor to impersonation, nor to user account, etc. However we suspect that this automation itself trigger at some point the issue. Excel app start be somehow blocked to be open by comobject in Powershell. As if something lost privileges to some object suddenly. Even the process is stucked (after first error appear) and direct Powershell does not work anymore, you still can open excel manually and refresh it without any issue.

I would guess the problem is connected to that OfficeClickToRun process. I was even thinking to auto reset this process after each excel refresh iteration to keep it "fresh". However for that automation i do no have appropriate privileges to run automatically Powershell as Admin. Or maybe there is a way?

Summarize:

Final notes:

We have all those Excel App DCOM object settings, Interactive user, Remote Execution Etc. Etc. We also have those Fakes "Desktop" folders created and other tricks which had to be done to make this automated process works. We have also try to uninstall Office and install fresh instance again, it did not help.

Questions: Do you have any suggestion what can cause this issue? Or how can we track this issue?

UPDATE: Maybe we have determine something. DCOM excel app is set as Interactive user (it is must for this process and was before, otherwise other users have not been able to execute process manually). But it seems all the auto refreshes fails when all users are Disconnected (not logged off) from machine for some time. As if the process is missing active user to be connected on machine. So issue may happened overnight when no one is working on Desktop or during a day, when no one is actively remotely connected to machine. Strange is that we did not have this behavior with previous Excel version.

Powershell code sample (only Excel related lines are below):

function ReleaseRef ($ref)
{
    if($ref -ne $Null)
    {   ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }
}


#Start of the Procedure:
MakeReadWrite $TemplateFilePath
$excel = new-object -comobject excel.application
#$Excel.Visible = $false

$workbook = $excel.Workbooks.Open($TemplateFilePath)
$workBook.RefreshAll()
$workbook.Save()
$workbook.Close($False, $TemplateFilePath, $False)


$a = ReleaseRef($worksheet)
$a = ReleaseRef($workbook)
$excel.Quit()
$a = ReleaseRef($excel)

Upvotes: 0

Views: 416

Answers (1)

S. Hurley
S. Hurley

Reputation: 75

Seems like there are several answers on how to do this on the web including this one: Updating-Excel-From-Powershell

I would compare what you have to something like that..

This Error: You cannot call a method on a null-valued expression

Generally means you are trying to run a method that does not exist or on an expression that does not exist. Example Task.Run() but not Task.Runt() or Taskt.Run()

Upvotes: 0

Related Questions