read only for password protected files.

[ldfreight]

Hello,

I have a xlsb file that is password protected for writing. When I call xl.read.file, it shows a popup asking for password, or I can select "Read only" which then works. However this popu is annoying as I have a lot of tabs to read. Is it possible to pass an argument in xl.read.file to select "read only" by default?

Many thanks

[gdemin]

Hi, By now there is no such argument. I will add it in the future version. As quick workaround you can use the following code:

xl.read.file2 = function(filename, header = TRUE, row.names = NULL, col.names = NULL, 
                        xl.sheet = NULL,top.left.cell = "A1", na = "",
                        password = NULL, write.res.password = NULL, read.only = FALSE,
                        excel.visible = FALSE)
{
    xl_temp = COMCreate("Excel.Application",existing = FALSE)
    on.exit(xl_temp$quit()) 
    xl_temp[["Visible"]] = excel.visible
    xl_temp[["DisplayAlerts"]] = FALSE
    if (isTRUE(grepl("^(http|ftp)s?://", filename))){
        path = filename
    } else {
        path = normalizePath(filename,mustWork = TRUE)  
    }
    passwords =paste(!is.null(password), !is.null(write.res.password), sep = "_") 
    xl_wb = switch(passwords, 
                   FALSE_FALSE = xl_temp[["Workbooks"]]$Open(path,
                                                             readonly = read.only),
                   TRUE_FALSE = xl_temp[["Workbooks"]]$Open(path, 
                                                            password = password,
                                                            readonly = read.only
                   ),
                   FALSE_TRUE = xl_temp[["Workbooks"]]$Open(path, 
                                                            writerespassword = write.res.password,
                                                            readonly = read.only
                   ),
                   TRUE_TRUE = xl_temp[["Workbooks"]]$Open(path, 
                                                           password = password, 
                                                           writerespassword = write.res.password,
                                                           readonly = read.only
                   )
    )    
    if (!is.null(xl.sheet)){
        data_sheet = excel.link:::get_sheet(xl_wb, xl.sheet)
    } else {
        data_sheet = xl_wb[["activesheet"]]
    }
    if(is.null(row.names) && is.null(col.names)){
        if(header){
            col.names = TRUE
            temp = excel.link:::xl.read.range(data_sheet$range(top.left.cell),na = "")
            row.names = is.na(temp) || all(grepl("^([\\s\\t]+)$",temp,perl = TRUE))
        } else {
            row.names = FALSE
            col.names = FALSE
        }
    } else {
        if (is.null(row.names)) row.names = FALSE
        if (is.null(col.names)) col.names = FALSE
    }
    top_left_corner = data_sheet$range(top.left.cell)
    xl.rng = top_left_corner[["CurrentRegion"]]
    if (tolower(top.left.cell) !=  "a1") {
        bottom_row = xl.rng[["row"]]+xl.rng[["rows"]][["count"]]-1
        right_column = xl.rng[["column"]]+xl.rng[["columns"]][["count"]]-1
        xl.rng = data_sheet$range(top_left_corner, data_sheet$cells(bottom_row,right_column))
    } 
    excel.link:::xl.read.range(xl.rng,drop = FALSE,na = na,row.names = row.names,col.names = col.names)
}

xl.read.file2(filename = "readonly.xlsb", read.only = TRUE)

[ldfreight]

it works like a charm, thank you! would be a great addition in next version indeed.