Convert .xlsm to .xlsx in R

Question

I would like to convert an Excel file (say it's name is "Jimmy") that is saved as a macro enabled workbook (Jimmy.xlsm) to Jimmy.xlsx.

I need this to be done in a coding environment. I cannot simply change this by opening the file in Excel and assigning a different file-type. I am currently programming in R. If I use the function

file.rename("Jimmy.xlsm", "Jimmy.xlsx")

the file becomes corrupted.

[a million ways to connect R and excel](http://www.thertrader.com/2014/02/11/a-million-ways-to-connect-r-and-excel/) is worth a read, as well as [Does the xlsx package work for xlsm files in R?](http://stackoverflow.com/a/11215451/4002530) — tospig, Apr 12 '15 at 22:24

score 3 · Accepted Answer · answered Apr 13 '15 at 22:07

In your framework you have to read in the sheet and write it back out. Suppose you have an XLSM file (with macros, I presume) called "testXLSM2X.xlsm" containing one sheet with tabular columns of data. This will do the trick:

library(xlsx)
r <- read.xlsx("testXLSMtoX.xlsm", 1) # read the first sheet
# provides a data frame
# use the first column in the spreadsheet to create row names then delete that column from the data frame
# otherwise you will get an extra column of row index numbers in the first column
r2w<-data.frame(r[-1],row.names=r[,1])
w <- write.xlsx(r2w,"testXLSMtoX.xlsx") # write the sheet

The macros will be stripped out, of course.

That's an answer but I would question what you are trying to accomplish. In general it is easier to control R from Excel than Excel from R. I use REXCEL from http://rcom.univie.ac.at/, which is not open source but pretty robust.

Emmanuel Hamel · Answer 2 · 2021-11-23T14:17:05.157

Here is a function that converts XLSM files to XLSX files with the R package RDCOMClient :

convert_XLSM_File_To_XLSX <- function(path_XLSM_File, path_XLSX_File)
{
  xlApp <- COMCreate("Excel.Application")
  xlApp[['Visible']] <- FALSE
  xlApp[["DisplayAlerts"]] <- FALSE
  xlWbk <- xlApp$Workbooks()$Open(path_XLSM_File)
  xlWbk$SaveAs(path_XLSX_File, 51)
  xlWbk$Close()
  xlApp$Quit()
}

library(RDCOMClient)
convert_XLSM_File_To_XLSX(path_XLSM_File, path_XLSX_File)

Convert .xlsm to .xlsx in R

2 Answers2