Edit excel property using RDCOMClient

Question

I am unable to find any documentation for RDComClient package. I am unable to find the suitable parameter to change the background fill for a cell.

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
book = xlApp$Workbooks()$Open("C:/Users/koyeli.majumder/Desktop/Dashboard/National Level Dashboard.xlsb")

sheet = book$Worksheets()$Item(1) 
xlApp[["Visible"]] = TRUE

cell  <- sheet$Cells(6,6)
cell[["Value"]] <- 3.1
cell[["NumberFormat"]]  = "[Red]"  # till this it was fine

cell[["Style"]] = 1

This gives error

<checkErrorInfo> 8002000E 
Error: Invalid number of parameters.

If you're willing to switch packages, [`openxlsx`](https://cran.r-project.org/web/packages/openxlsx/index.html) is very well documented — camille, May 03 '18 at 11:47
I want to open the excel and do it visibly, hence was using RDCOMClient. — Koyeli, May 03 '18 at 12:42
openxlsx does not allow add images as headers so I need RDCOMClient — Alvaro Morales, May 16 '21 at 21:59

G. Grothendieck · Answer 1 · 2018-05-03T22:03:33.597

2

RDCOMClient is a general COM interface. It is not specific to Excel. You will need to refer to general Excel documentation which typically refers to Basic or javascript and translate it to R.

Interior color is set as shown below. The color index numbers can be found here: https://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx

library(RDCOMClient)

xl <- COMCreate("Excel.Application")
xl[["Visible"]] <- TRUE
wkbk <- xl$Workbooks()$Add()
sheet <- xl$ActiveSheet()

x12 <- sheet$Cells(1,2)
x12[["Value"]] <- 123
x12[["Interior"]][["ColorIndex"]] <- 3  # Red

edited May 03 '18 at 22:03

answered May 03 '18 at 16:33

G. Grothendieck

211,268
15
177
297

It worked. Thanks! Can you please share any link where I can find this- "Interior", "ColorIndex", etc different other properties for excel. – Koyeli May 04 '18 at 12:19
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/colorindex-property – G. Grothendieck May 04 '18 at 13:56

Edit excel property using RDCOMClient

1 Answers1