CODEWITHSUNDEEP
excelpowershell
Michelle Santos
Michelle Santos

Reputation: 267

How to add formula to Excel

My question is how could I place this formula into a cell using PowerShell:

="('"&B6&"','"&TEXT(C6,"mm-dd-yyyy hh:mm")&"','"&D6&"','"&E6&"','"&F6&"','"&G6&"','"&H6&"','"&I6&"','"&J6&"','"&K6&"','"&L6&"','"&M6&"','"&N6&"','"&O6&"','"&P6&"'),"

I use it to generate a insert T-SQL statement from the content of my excel file. Whenever I place the formula into a cell using PowerShell I get an error. You may see reference image below.

Here's the error message I get when executing my script

This is how I've been doing it:

$Sheet.cells.item(6,43) = “=““('““&B6&”“','““&TEXT(C6,”“mm-dd-yyyy hh:mm”“)&”“','““&D6&”“','““&E6&”“','““&F6&”“','““&G6&”“','““&H6&”“','““&I6&”“','““&J6&”“','““&K6&”“','““&L6&”“','““&M6&”“','““&N6&”“','““&O6&”“','““&P6&”“'),”“”

Upvotes: 1

Views: 886

Answers (2)

Michelle Santos
Michelle Santos

Reputation: 267

After multiple trials using escape enabled me to land on my desired output:

 $Sheet.cells.item(6,43) = "=`"(`'`"&b6&`"`',`'`"&TEXT(c6,`"mm-dd-yyyy hh:mm`")&`"`',`'`"&d6&`"`',`'`"&f6&`"`',`'`"&g6&`"`',`'`"&h6&`"`',`'`"&i6&`"`',`'`"&j6&`"`',`'`"&k6&`"`',`'`"&l6&`"`',`'`"&m6&`"`',`'`"&n6&`"`',`'`"&o6&`"`',`'`"&p6&`"`'),`""

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

You assign formulas via the Formula (or FormulaR1C1) property:

$Sheet.Cells.Item(6, 43).Formula = "=`"('`"&B6&...&P6&`"'),`""

As a side note: you should avoid using typographic quotes. PowerShell sometimes treats them as straight quotes, but in general they tend to cause problems.

Upvotes: 2

Related Questions