Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

powershell script to enter formula into an excel spreadsheet

Posted on 2014-04-06
3
Medium Priority
?
1,663 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 71

Accepted Solution

by:
Qlemo earned 2000 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 71

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

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

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

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Loops Section Overview

963 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