CODEWITHSUNDEEP
excelpowershellexcel-formula
hostyd
hostyd

Reputation: 31

Insert complicated Excel formula using Powershell

Here is original article: link.

Now my question:

Using the .formula, I can get very far, but I have a very complicated formula and it doesn't want to work.

The code that works in Excel:

=IF(ISBLANK(G2),"",VLOOKUP("*"&LEFT(G2,9)&"*",'\\compname\path\to\excel\[ramdata.xlsx]20151009'!A:B,2,FALSE))

The code that does not work in Powershell:

$ws.Cells.Item($intRow,9).Formula = '=VLOOKUP("*"$([char]38)LEFT(G$intRow,5)$([char]38)"*",'\\compname\path\to\excel\[ramdata.xlsx]20151009'!A:B,2,FALSE)'

After I run the powershell script, the cell in which I am inserting the formula is blank.

Any ideas?

Upvotes: 1

Views: 1849

Answers (1)

hostyd
hostyd

Reputation: 31

I was so close and needed to ask for help to find the answer!

$ws.Cells.Item($intRow,9).Formula = "=IF(ISBLANK(G$intRowMem),$([char]34)$([char]34),VLOOKUP($([char]34)*$([char]34)&LEFT(G$intRowMem,5)&$([char]34)*$([char]34),'\\compname\path\to\excel\[ramdata.xlsx]20151009'!A:B,2,FALSE))"

Is it ethical to answer your own question and mark your answer as the answer?

EDIT

Per @TheMadTechnician, this can also be solved as:

$ws.Cells.Item($intRow,9).Formula = "=IF(ISBLANK(G$intRowMem),`"`",VLOOKUP(`"*`"&LEFT(G$intRowMem,5)&`"*`",'\\compname\path\to\excel\[ramdata.xlsx]20151009'!A:B,2,FALSE))"

Upvotes: 2

Related Questions