Solved

powershell script to enter formula into an excel spreadsheet

Posted on 2014-04-06
3
1,211 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 68

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 68

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now