Insert complicated Excel formula using Powershell

Question

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?

hostyd · Accepted Answer · 2015-10-12 19:29:03Z

2

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))"

edited Oct 12, 2015 at 19:29

answered Oct 12, 2015 at 17:29

hostyd

317 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheMadTechnician Over a year ago

Wow, you went through a lot of trouble to get those double quotes in there... in the future you can just escape them with a backtick (`) within your string to get them there, or double them up such as "The path ""C:\Temp"" doesn't actually need quotes around it" will appear as The path "c:\temp" doesn't actually need quotes around it. And it's just fine to accept your own answer if it actually answers the question.

hostyd Over a year ago

I had used the backtick, but did not think it worked. What really happened is that I was trying to many things at once. I did try it and LONG and BEHOLD it worked just fine.

Robin Gertenbach Over a year ago

You can do it but you need to wait a bit: stackoverflow.com/help/accepted-answer

Mikael Dyreborg Hansen Over a year ago

I know this is an old one by now, but THANK YOU! - Been pulling my hair for hours trying to figure this out! - i had tried the approach using ´"´" But didn't realize I had used the incorrect character to escape (´ instead of `)

Collectives™ on Stack Overflow

Insert complicated Excel formula using Powershell

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related