Evaluate Excel File Without Opening/Saving

user_9807503 0
Viewed 1395

Is there a way to calculate excel formulas without opening the file manually? If openxlsx is not the best option please feel free to suggest other packages. Thanks!

My goal is to paste data into an excel file (already existing) with formulas referencing the range that i am pasting data to and have excel refresh it's formulas and calculate summary stats(sums for example). I would like to update the formulas with R code and save the file so that i can read the summary values into R without opening/saving the excel file.

I want to remove the manual step (Bold below).

Workbook that i am using has two sheets: rawData and summary. the only data before i start in the file is the sum formulas on A2:D2. A2 contains =SUM(rawData!A:A)

require("openxlsx")
wb <- loadWorkbook("MyTestWorkBook.xlsx")
writeData(wb,iris, sheet="rawData")
saveWorkbook(wb,"MyTestWorkBook.xlsx",overwrite = TRUE)

From here I have the sheet 'summary' with sums of each column. I read the sheet. 

read.xlsx("MyTestWorkBook.xlsx","summary")
  sumA sumB sumC sumD
1    0    0    0    0

I now manually open workbook (not with R) and save

read.xlsx("MyTestWorkBook.xlsx","summary")
   sumA  sumB  sumC  sumD
1 876.5 458.6 563.7 179.9

Formulas have now calculated.

openxlsx r
3 Answers

I figured out a way to deal with this, but it is not the most elegant. The solution is executing the following function right after you use saveWorkbook() and before you read data from the workbook you just saved:

calculate_wb_fn <- function(excel_file_directory, excel_file_name)
{
  macro_calculate_wb <- file(paste0(excel_file_directory,"macro_calculate_wb.vbs") )
  writeLines(c("Const xlVisible = -1",
               "Dim objExcel",
               "Dim objWb",
               "Dim objws",
               "Dim strFileName",
               paste0("strFileName = \"",gsub("/","\\\\",excel_file_directory),excel_file_name,"\""),
               "On Error Resume Next",
               "Set objExcel = CreateObject(\"excel.application\")",
               "Set objWb = objExcel.Workbooks.Open(strFileName)",
               "objExcel.DisplayAlerts = False",
               "objWb.Save",
               "objWb.Close SaveChanges=True",
               "objExcel.Close",
               "objExcel.Quit",
               "set objWb = Nothing",
               "set objExcel = Nothing",
               "On Error GoTo 0")
             , macro_calculate_wb)

  close(macro_calculate_wb)


  file <- normalizePath(paste0(excel_file_directory,"macro_calculate_wb.vbs") )


  shell(shQuote(string = file), wait = T)

  file.remove(paste0(excel_file_directory,"macro_calculate_wb.vbs"))
}

The function creates a file with a VBscript that opens the file, saves it (i.e., also calculates it), and closes it. Then it executes the script, and then it deletes the script.

user_95001650

A bit late, but I found the RDCOMClient package a simple way to do this.

outputfile <- "filenameblahblah.xlsx"

library(RDCOMClient)

# Create COM Connection to Excel
xlApp <- COMCreate("Excel.Application")
xlApp[['Visible']] <- FALSE
xlApp[['DisplayAlerts']] <- FALSE

# Open workbook
xlWB <- xlApp[["Workbooks"]]$Open(outputfile)
xlWB$Save()
xlWB$Close(TRUE)

Note that RCDOMClient apparently only works on Windows machines and you may have have to install it using install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")

user_123702360

The following function works on my end :

force_Calculation_Excel_Formula <- function(file_name)
{
    library(excel.link)
    app <- xl.workbook.open(filename = file_name)
    app[["Statusbar"]] <- ""
    app[["Screenupdating"]] <- TRUE
    app[["Calculation"]] <- xl.constants$xlCalculationManual
    invisible(NULL)
    xl.workbook.save(filename_Save)
    xl.workbook.close()
    system("taskkill /IM Excel.exe")
}
user_92456230
Related