我找到了解决这个问题的方法,但这不是最优雅的。解决方案是在使用 saveWorkbook() 之后、从刚刚保存的工作簿中读取数据之前立即执行以下函数:
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"))
}
该函数使用 VBscript 创建一个文件,打开该文件,保存它(即,也计算它),然后关闭它。然后它执行该脚本,然后删除该脚本。