Solved

powershell script to enter formula into an excel spreadsheet

Posted on 2014-04-06
3
1,355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

762 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