CODEWITHSUNDEEP
rexcelrdcomclient
Koyeli
Koyeli

Reputation: 67

Edit excel property using RDCOMClient

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.

Upvotes: 2

Views: 2303

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269371

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

screenshot

Upvotes: 3

Related Questions