1
Vote

Set-SLCellValue not working

description

Set-SLCellValue doesn't work, however I can work around by using Set-SLColumnValue or Set-SLRowValue with a single cell reference.

comments

bridgesd2 wrote Jul 11, 2015 at 11:22 PM

The other workaround that works is to use this syntax:

$path = "D:\Powershell\Excel\SLPSlib\"
$WB = 'WorkBook'
$WS = 'Test'

New-SLDocument -WorkbookName $WB -WorksheetName $WS -Path $path -Force -Confirm:$false

$doc = Get-SLDocument -Path ($path + $WB + '.xlsx')

$doc.SetCellValue(1,1,'testing')
$doc.SetCellValue(2,2,1234)

Save-SLDocument -WorkBookInstance $doc

AdeGrover wrote May 16, 2016 at 3:19 PM

I tend to use the $doc.SetCellValue() workaround by default so I hadn't noticed this until now. I'm currently writing some stuff up to encourage my team members to user PS for more things, and was trying to create some examples using the "proper" commands when I discovered it. Spend a couple or hours thinking I must be doing something stupid. Looks to me like there is an error in the parameterset definitions in the code for Set-SLCellValue in SLPSLib.psm1 so that the code to actually set the cell value can never be called.

I have changed the code to the following in my copy and it seems to work OK, but I have not extensively tested it.
[parameter(Mandatory=$true,Position=2,ValueFromPipeLineByPropertyName=$true,Parametersetname='Singlecell')]
[String[]]$CellReference,

[parameter(Mandatory=$true,Position=3,ValueFromPipeLineByPropertyName=$true)]
$value,