[SOLVED] RDCOMClient - Set Vertical Range Values

RDCOMClient - Set Vertical Range Values

I am trying to write values into a vertical Range using the RDCOMClient library. Since the RDCOMClient allows you to write code very similar to VBA, I've been looking for ways to do this in VBA and then translate to R.

The base R code to create the excel is:

app <- COMCreate("Excel.Application")
app[['Visible']] <- TRUE

workbook <- app$WorkBooks()$Open(path)
sheet <- workbook$Worksheets(sheet_name)
sheet$Activate()

If my Range was horizontal then I could simply write it as:

VBA
```
Range("A1:C1").Value = Array(1,2,3)
```

range <- sheet$Range('A1:C1')
range[['Value']] <- c(1,2,3)

Since the Range is vertical the code is:

VBA

Range("A1:A3").Value = Application.Transpose(Array(1, 2, 3))

range <- sheet$Range('A1:A3')
range[['Value']] <- app$Transpose(c(1,2,3))

The problem is that the R "translation" for the vertical case does not work.

Printing the range[['Value']] in the console shows:

[[1]]
[[1]][[1]]
NULL

[[1]][[2]]
NULL

[[1]][[3]]
NULL

And the app$Transpose(c(1,2,3)) shows:

[[1]]
[[1]][[1]]
[1] 1

[[1]][[2]]
[1] 2

[[1]][[3]]
[1] 3

Why is it not setting the values correctly? What is the correct way to do this?

I also tried using a matrix:

range[['Value']] <- matrix(nrow = 3, c(1,2,3))

But it gives an error:

Can't attach the RDCOMServer package needed to create a generic COM object

I also tried setting the range[['Value']][[1]] directly with no success.

Solution

Put vertical 1:7 in the cells B3 to B9:

r= sheet$Range("B3:B9")
r[["Value"]] <- asCOMArray(matrix(1:7, 7, 1))