Powershell通用错误:Exception from HRESULT: 0x800A03EC

Question

我知道这是一个非常普遍的错误信息，我花了很多时间查看类似的问题，但没有任何运气。

这是我的PowerShell脚本产生这个错误:

$ExcelObject = New-Object -ComObject excel.application
$excelProcessId = ((get-process excel | select MainWindowTitle, ID, StartTime | Sort StartTime)[-1]).Id
$ExcelObject.visible = $true
$ExcelObject.DisplayAlerts = $true
$folderpath = "C:myfilesTESTER.xlsx"
$Workbook = $ExcelObject.Workbooks.Open($folderpath)
$WorkSheet = $Workbook.Worksheets.Item("TAB1")
$sourceList = "value1,value2,value3"
$missing = [system.type]::missing
$sourceRange = $WorkSheet.Range("A3:A22")
$sourceRange.Validation.add(3,1,$missing,$sourceList,$missing)
$Workbook.save()
$Workbook.close()
$ExcelObject.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObject)
[GC]::Collect()
Remove-Variable ExcelObject
Stop-Process -Id $excelProcessId -Force

产生的错误:

Exception from HRESULT: 0x800A03EC
At line:11 char:1
+ $sourceRange.Validation.add(3,1,$missing,$sourceList,$missing)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

我已经尝试直接使用值列表，但同样的错误:

$sourceRange.Validation.add(3,1,$missing,"value1,value2,value3",$missing)

我替换了$，但是同样的错误:

$sourceRange.Validation.add(3,1,1,$sourceList)

我对如何解决这个问题没有更多的想法，欢迎您的建议。非常感谢你的时间。

Answer 1

我发现了问题:指定的范围必须清除任何现有的验证:

$sourceRange = $WorkSheet.Range("A3:A22")
$sourceRange.Validation.Delete()
$sourceRange.Validation.add(3,1,$missing,$sourceList,$missing)

我觉得有点尴尬，但也许这对将来的人有用。