Solved

powershell script to enter formula into an excel spreadsheet

Posted on 2014-04-06
3
1,267 Views
Last Modified: 2014-04-06
Hi,

I have a report which I would like to create automated excel querys using a scripted apporach. I've come into an issue with using double quotations with creating formulas in powershell code. I just wanted help with regards to placing it into a powershell variable

$forumal1 = @"
=COUNTIFS(sheet2!C:C,"Bermuda", sheet2!I:I, "dynamite")
"@

This would error in powershell as Excel would require the double quotes when preforming actions from multiple sheets.

I was wondering if someone could help me understand $([char]34) using the $formula1 as an example?
0
Comment
Question by:patelbg2001
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39981522
What's the error you get with above code? It should work, as you can use double quotes freely inside of a here-string (@" or @').
If you would like to have it as "normal" string, there are several ways to do that:
$formula1 = '=COUNTIFS(sheet2!C:C,"Bermuda", sheet2!I:I, "dynamite")'
$formula1 = "=COUNTIFS(sheet2!C:C,""Bermuda"", sheet2!I:I, ""dynamite"")"
$formula1 = "=COUNTIFS(sheet2!C:C,`"Bermuda`", sheet2!I:I, `"dynamite`")'

Open in new window

The first one uses a single quoted string - there will be no processing of the contents (no variable substitution, no escape codes like new line (`n), etc.)
The second one is the preferred, as it does not have any side effects aside of having to double each double quote.
The last escapes each inside double quote with a backtick.
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39981544
worked it out "$($name)"
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39981571
You don't need the subexpression syntax, "$name" is sufficient. $(...) is only needed if you want to access an object's property inside of the double quotes, or require an expression instead of a simple var, like with "$(1+1)" or "$($var.Length)".
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Set OWA language and time zone in Exchange for individuals, all users or per database.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question