R Tool to Read from a password protected XLSM Macro Enabled File

[aaronindm]

I was successful in using the R TOol Code below to read ffrom an XLSX file that was password protected WITHOUT the red parameter below.

Researching xl.read.file (https://www.rdocumentation.org/packages/excel.link/versions/0.9.8-1/topics/xl.read.file)

I have found that the file.format parameter is optional, and when NOT present it defaults to file.format = xl.constants$xlOpenXMLWorkbook.  However, when I include that when reading an XLSX file, it does not work.

The problem I am trying to solve is using the code below to read from an XLSM file, but don't seem to be using the file.format parameter correctly.

>>>>>>>>>>

install.packages(c("excel.link"),repos='https://cran.revolutionanalytics.com/',dependencies = TRUE)
library("excel.link")

df <- read.Alteryx("#1", mode="data.frame")
importfilename <- as.character(df[1,"FullPath"])
excel_pwd <- as.character(df[1,"excel_password"])
excel_sheet <- as.character(df[1,"Sheet_Name"])

excel_data <- xl.read.file(file.format = xl.constants$xlOpenXMLWorkbookMacroEnabled, importfilename, xl.sheet = excel_sheet, password = excel_pwd)

write.Alteryx(excel_data, 1)

[PaulNo · Accepted solution]

Hi @aaronindm,

As per documentation, argument file.format is only available for xl.save.file. Using it with xl.read.file wil throw the following error:

Error in xl.read.file(file.format = xl.constants$xlOpenXMLWorkbookMacroEnabled, : 
unused argument (file.format = xl.constants$xlOpenXMLWorkbookMacroEnabled)

Thanks,

PaulN

[aaronindm]

@PaulNo thank you - I was not sure if those arguments could/should be used in both read and write, but apparently it is just the write functions!

It does work now, sort of. 

I am still having issues with the R Tool import not accepting dates, but I have that in a separate thread here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-R-to-Read-in-Password-Protected-...