Using paste() and writeFormula() to create a descending series of formulas that sums values across rows using the INDIRECT() function in Excel. This particular function is notable because it requires quotation marks within it, which seems to be giving R problems. In order to write the formulas in R that I then want to appear in Excel, I am using paste() to pair INDIRECT with an index moving down the column. For example:
for (i in 1:20){
test_formulas[i] <- paste('INDIRECT("Worksheet2!"&"B',(i+2),'")',sep="")
This should then reference the cell @ Worksheet2!B3, but an issue occurs. While I do not have to type backslash characters to exit the double-quotation marks, R inserts them automatically. While this is not a problem in R — R does not see these backslashes, evidenced by using cat() to display the output of the paste — writeFormula() does see them. In other words, I would expect the result of the paste to be:
'INDIRECT("Worksheet2!"&"B[i=1:20]")'
But the result ends up being:
'INDIRECT(\"Worksheet2!\"&\"B[i=1:20]\")'
Actual Behavior
No errors are shown in R, either after writeFormula() or after opening the spreadsheet. In Excel, however, an error is displayed saying "We found a problem with some content in 'TEST.xlsx'. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes."
If the yes option is selected, the file appears as intended, except all formulas that should have contained the escape backslash in R are entirely absent.
Steps to Reproduce the Problem
The stripped-down version of the formula that results in error:
Desired formula in excel:
=INDIRECT("'Worksheet2'!"&"B"&A1)
=INDIRECT("'Worksheet2'!"&"B"&A2)
...
=INDIRECT("'Worksheet2'!"&"B"&A20)
Inputs to R:
test_formulas <- rep(0,length(test))
for (i in 1:length(test)){
test_formulas[i] <- paste('INDIRECT("Worksheet2!"&"B',(i+2),'")',sep="")}
writeFormula(wb,"Test",test_formulas,startCol=2,startRow=1)
Using cat(test_formulas), it is obvious that R does not read the backslashes, but this error in Excel happens consistently when trying to write formulas containing backslashes.
Thoughts on this? Intended behavior & I'm just misusing the tool, or something else?
Thanks,
Dakota
Notably — switching the order of the double- & single-quotation marks DOES prevent R from introducing the backslash characters, but Excel won't accept INDIRECT() inputs in single-quotations.
Expected Behavior
Using paste() and writeFormula() to create a descending series of formulas that sums values across rows using the INDIRECT() function in Excel. This particular function is notable because it requires quotation marks within it, which seems to be giving R problems. In order to write the formulas in R that I then want to appear in Excel, I am using paste() to pair INDIRECT with an index moving down the column. For example:
for (i in 1:20){ test_formulas[i] <- paste('INDIRECT("Worksheet2!"&"B',(i+2),'")',sep="")
This should then reference the cell @ Worksheet2!B3, but an issue occurs. While I do not have to type backslash characters to exit the double-quotation marks, R inserts them automatically. While this is not a problem in R — R does not see these backslashes, evidenced by using cat() to display the output of the paste — writeFormula() does see them. In other words, I would expect the result of the paste to be:
'INDIRECT("Worksheet2!"&"B[i=1:20]")'
But the result ends up being:
'INDIRECT(\"Worksheet2!\"&\"B[i=1:20]\")'
Actual Behavior
No errors are shown in R, either after writeFormula() or after opening the spreadsheet. In Excel, however, an error is displayed saying "We found a problem with some content in 'TEST.xlsx'. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes." If the yes option is selected, the file appears as intended, except all formulas that should have contained the escape backslash in R are entirely absent.
Steps to Reproduce the Problem
The stripped-down version of the formula that results in error: Desired formula in excel: =INDIRECT("'Worksheet2'!"&"B"&A1) =INDIRECT("'Worksheet2'!"&"B"&A2) ... =INDIRECT("'Worksheet2'!"&"B"&A20)
Inputs to R: test_formulas <- rep(0,length(test)) for (i in 1:length(test)){ test_formulas[i] <- paste('INDIRECT("Worksheet2!"&"B',(i+2),'")',sep="")} writeFormula(wb,"Test",test_formulas,startCol=2,startRow=1)
Using cat(test_formulas), it is obvious that R does not read the backslashes, but this error in Excel happens consistently when trying to write formulas containing backslashes. Thoughts on this? Intended behavior & I'm just misusing the tool, or something else? Thanks, Dakota
Notably — switching the order of the double- & single-quotation marks DOES prevent R from introducing the backslash characters, but Excel won't accept INDIRECT() inputs in single-quotations.