#=========================================================================# # Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow # by Sang-Heon Lee # # https://kiandlee.blogspot.com #————————————————————————-# # Read and Write Excel in R, also call VBA macro in R using RDCOMClient # # Install package (Not available on CRAN at 12 June 2019) # install.packages(“RDCOMClient”, repos = “http://www.omegahat.net/R”) #=========================================================================# library(RDCOMClient) graphics.off() # clear all graphs rm(list = ls()) # remove all files from your workspace #=========================================================== # functions using RDCOMClient #=========================================================== f_read_vector – function(xlWbk1, sheet1, range1){ sheet – xlWbk1$Worksheets(sheet1) range – sheet$Range(range1) data – as.numeric(unlist(range[[“Value”]])) return(data) } f_write_vector – function(xlWbk1, sheet1, range1, data1) { sheet – xlWbk1$Worksheets(sheet1) range – sheet$Range(range1) range[[“Value”]] – asCOMArray(data1) } #=========================================================== # MAIN #=========================================================== # set working directory setwd(“D:/SHLEE/blog/excel_com”) # Create Excel Application xlApp – COMCreate(“Excel.Application”) # Open the Macro Excel book fn – “sample_excel.xlsm” xlWbk – xlApp$Workbooks()$Open(paste0(getwd(),“/”,fn)) # use TRUE for Excel Spreadsheet to be visible xlApp[[‘Visible’]] – FALSE #=========================================================== # Communicate between R and Excel #=========================================================== # Arguments for Excel Spreadsheet and VBA macro sheet – “Sheet1” range_in – “C3:C12” range_out – “D3:D12” macro_name – “macro1” … Read more