CODEWITHSUNDEEP
excelpowershellexcel-formula
João Ciocca
João Ciocca

Reputation: 866

Unable to change formula on a cell?

Following up from this question, I'm trying to replace $B$1 to TEXT($B$1,"0000") on all formulas I can find on a lot of workbooks. Now that i'm past that .save() problem, I've got another (which should've been the first, actually): I can't seem to change .Formula value, no matter what I try.

PS C:\> $Search.Formula = $Search.Formula -replace '\$B\$1','TEXTO($B$1,"0000")'
Exceção ao definir "Formula": "Exceção de HRESULT: 0x800A03EC"
No linha:1 caractere:1
+ $Search.Formula = $Search.Formula -replace '\$B\$1','TEXTO($B$1,"0000")'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterSetValueTI

Formula is:

=PROCV(("A"&ANO($A6)&"M"&MÊS($A6)&"P"&$B$1);BASE!$A:$P;9;FALSO)

In English, if I remember the correlation correctly:

=VLOOKUP(("A"&YEAR($A6)&"M"&MONTH($A6)&"P"&$B$1);BASE!$A:$P;9;FALSE)

The expected output would be 

=VLOOKUP(("A"&YEAR($A6)&"M"&MONTH($A6)&"P"&TEXT($B$1,"0000"));BASE!$A:$P;9;FALSE)

Upvotes: 1

Views: 151

Answers (1)

user4039065
user4039065

Reputation:

There were a couple of things going on with what you supplied. At first glance, you seem to be using the backslash for an escape character to make the dollar signs literal. The escape character for this in PowerShell is the back-tick or grave (e.g. `).

If I was performing this action within Excel, I would probably just Find & Replace every $B$1 on the worksheet with text($B$1, "0000"). Seems to me that it is powerful enough to take care of the operation without PowerShell's -replace method. The worksheet method does depend somewhat on $B$1 begin available but since it is also in the replacement, you pretty much need to know what you are replacing beforehand. Some error control in that area may be necessary if this script is left for casual users.

$excel = New-Object -comobject Excel.Application
$FilePath = "c:\temp\example.xlsx"
$workbook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $true
$worksheet = $workbook.worksheets.item("Sheet1")
#set some Find & Replace vars
$what = "`$B`$1"
$with = "text(`$B`$1, `"0000`")"
#use worksheet-wide Find & Replace to change formula
$worksheet.usedrange.replace($what, $with, 2)
#formula(s) should be changed. now Find it and display it
$fnd = $worksheet.usedrange.find($what, $worksheet.range("A1"), -4123, 2)
Write-Output $fnd.formula
$workbook.save()
$workbook.close()
$excel.quit()

I've proofed the Range.Replace method by finding and displaying the formula after the operations and made more extensive use of the grave escape character rather than swap back and forth between single quotes and double quotes within quoted strings.

The above code uses the EN-US version I tested with. The actual replacement text for your regional settings would seem to be,

$with = "texto(`$B`$1; `"0000`")"

Upvotes: 2

Related Questions