2

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)

  • R

    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))

  • R

    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.

Daniel
  • 639
  • 8
  • 24
  • Does not matter if print before or after the result is the same. I know they do the same, but the way you write the code is a bit different, that's why I said very similar. @Parfait – Daniel May 04 '18 at 16:08
  • Print console and excel. The print output is the same before and after assign (no value). The excel stays blank with no values. – Daniel May 04 '18 at 16:35
  • You can run the code, it is all there. Just change the paths. – Daniel May 04 '18 at 16:36
  • I'm seeing the same. I worked around it by something like `values <- 1:3; rng <- sheet$Range("A3:A5"); for (i in 1:rng$Cells()$Count()) { cell <- rng$Cells(i); cell[["Value"]] <- values[i] }`. – Jon Olav Vik May 10 '20 at 20:46

1 Answers1

1

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

r= sheet$Range("B3:B9")
r[["Value"]] <- asCOMArray(matrix(1:7, 7, 1))
Sinval
  • 1,315
  • 1
  • 16
  • 25
user15221546
  • 26
  • 1