[R] Equation with double quotes from R to Excel?

[Liao, Hongsheng]

Sarah,
Thank you very much.  The following codes you sent to me work perfectly.  "IF(A2=\"\",\"\",1)" in R becomes "IF(A2="","",1)" in Excel.  Wonderful!

Hank

Hongsheng (Hank) Liao, PhD.
Lab Manager
Center for Quantitative  Fisheries Ecology
Old Dominion University
757-683-4571




> options(useFancyQuotes=FALSE)
> paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")

-----Original Message-----
From: Sarah Goslee [mailto:sarah.goslee at gmail.com] 
Sent: Friday, May 13, 2016 9:41 AM
To: Liao, Hongsheng
Cc: R-help Mailing List
Subject: Re: [R] Equation with double quotes from R to Excel?

This doesn't answer your actual question, but isn't it better practice to use ISBLANK instead of ""?

As for your actual question, a check of the parts of your command at the R prompt would probably reveal something interesting:

> paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=“”,“”,1)"
> options(useFancyQuotes=FALSE)
> paste("IF(A2=", dQuote(""), ",", dQuote(""), ",1)", sep="")
[1] "IF(A2=\"\",\"\",1)"

This bit of your output should have been a hint:
IF(A2=Ҡ,Ҡ,1)

Sarah

On Fri, May 13, 2016 at 9:24 AM, Liao, Hongsheng <HLiao at odu.edu> wrote:
> I am trying to add an equation with “” from R to an Excel workbook.  However, I have found that the Function “setCellFormula” doesn’t take the “” well while a “” in Excel equation stands for a blank cell.  I have tried NA(), EMPTY(), etc, and none of them are what I want.  Does anyone have other ways to put an equation with “” from R to Excel workbook?  Thanks in advance.
>
> Following is simple codes just for the demonstration, and the error is highlighted in red.
>
> #load package "XLConnect" to add functions from R to Excel
> library(XLConnect)
>
> # Load workbook (create if not existing) wb <- loadWorkbook("try 
> adding an equation.xlsx", create = TRUE)
>
> # Create a sheet named 'data'
> createSheet(wb, name = 'data')
>
> #the formula contains no double quotes which works well 
> formula.without.double.quote <- "IF(A2>95, 1, 0)"
> setCellFormula(wb, 'data', 2, 10, 
> formula=formula.without.double.quote)
>
> #the formula contains double quotes which doesn’t work with the function “setCellFormula”
> formula.with.double.quote <- paste("IF(A2=", dQuote(""), ",", 
> dQuote(""), ",1)", sep="") setCellFormula(wb, 'data', 3, 10, 
> formula=formula.with.double.quote)
> Error: FormulaParseException (Java): Parse error near char 6 '“' in 
> specified formula 'IF(A2=Ҡ,Ҡ,1)'. Expected cell ref or 
> constant literal
>
> #save the workbook
> saveWorkbook(wb, file="C:\\Users\\try adding an equation.xlsx")
>
> Hank
>
> Hongsheng (Hank) Liao, PhD.
> Lab Manager
> Center for Quantitative  Fisheries Ecology Old Dominion University
> 757-683-4571
>
>
>
>