Function in Bert

[naiiiiii]

Hello ! I want to define an function like this :

import_in_excel <- function(path) { #Chemin du dataset df <- read.csv(path) df_matrix <- as.matrix(df)

range <- EXCEL$Application$get_Range("A1:Z50000") range$put_Value(df_matrix) }

I want to import my csv in a notebook but when I try my function in excel I just have zéro as value,

thank you,

[amaeckelberghe]

Hi Naiiiiii,

I think the problem might have to do with the size of your range ("A1:Z50000"). I remember once having similar intentions in a script of mine and I ran into problems because the selected range was too large. It might be better to select a range with the exact size of your df.

You could use the following function to obtain the Column Letter associated with the number of columns of your df:

excelletter = function(col){ firstletter = ifelse(col>26,ifelse(col%%26!=0, letters[col%/%26], letters[(col-1)%/%26]),"") lastletter = ifelse(col>26,ifelse(col%%26!=0, letters[col%%26], "z"),letters[col]) exccol = toupper(paste0(firstletter,lastletter)) } paste0( "A1:", excelletter(ncol(df)), nrow(df) )

I think this should then return the range you need. Not sure whether this will help you, but it's worth a shot perhaps?

Kind regards,

Arno

[mikecrobe]

Did you have any success with this? I am trying to do something similar writing a range using put_Value but only managing to change one cell instead of the whole sheet.

As an aside, the process of creating a range from 2 dimensions is common in Excel VBA. Resize is the function and I notice it is a method of range in BERT too..