Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1890
  • Last Modified:

powershell script to enter formula into an excel spreadsheet

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
patelbg2001
Asked:
patelbg2001
  • 2
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
patelbg2001Author Commented:
worked it out "$($name)"
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now