Site icon R-bloggers

RDCOMClient : read and write Excel, and call VBA macro in R

sang-heon lee

[This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
This post gives an short introduction to the RDCOMClient R package, which provides a variety of functionalities. Our focus is on reading from and writing to Excel, and call VBA macro function in R.


Introduction


Using RDCOMClient R package, we can read from and write to Excel spreadsheet, furthermore, call VBA macro function in R. Of course this VBA is running in Excel not R. We can get results which are returned from VBA macro which was developed earlier.

This feature is very useful when this macro calls DLL, which is hard to be called in R for some reason. At last, we can get the same effect of calling DLL in R indirectly by using RDCOMClient package.


Installing


Unlike other packages, RDCOMClient is installed using the following command.

install.packages(“RDCOMClient”,repos=”http://www.omegahat.net/R”)


Excel Example with VBA macro


The following figure shows the operation of macro1() function by clicking [run macro1] rectangular button. macro1() returns squares of input variables simply.


The VBA code of macro1() is as follows.

1
2
3
4
5
6
7
8
9
Sub macro1()
 
    For i = 1 To 10
        Worksheets(“Sheet1”).Range(“D2”).Offset(i, 0).Value _
        = WorksheetFunction.Power( _
            Worksheets(“Sheet1”).Range(“C2”).Offset(i, 0).Value, 2)
    Next
    
End Sub
cs


R code


The following R code implements three operations:

  1. Write input array to Excel
  2. Run macro1()
  3. Read output array from Excel

R code is simple and self-contained so that you can easily understand it. For convenience, we make two functions : f_read_vector() and f_write_vector().

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
#=========================================================================#
# 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”
    
    #—————-
    # Example 1
    #—————-
    
    # 1) write input values from R to Excel
    data_in <– 1:10
    f_write_vector(xlWbk, sheet, range_in, data_in) 
    
    # 2) run Excel macro
    xlApp$Run(macro_name)
    
    # 3) read output values from R to Excel
    data_out <– f_read_vector(xlWbk, sheet, range_out)
 
    print(cbind(data_in, data_out))
    
    #—————-
    # Example 2
    #—————-
    
    data_in <– rnorm(10)
    f_write_vector(xlWbk, sheet, range_in, data_in) 
    xlApp$Run(macro_name)
    data_out <– f_read_vector(xlWbk, sheet, range_out)
    print(cbind(data_in, data_out))
    
#===========================================================
# save and quit
#===========================================================
xlWbk$close(TRUE); xlApp$Quit() 
    
cs


Results


The following console shows two outputs for each input array. This output is calculated in Excel by using VBA function not by using R.

In fact, running the above R code results in the following calculation outputs of Excel spreadsheet. R is only to read these results.


Conclusion


This post deals with a simple exercise (reading and writing) of RDCOMClient R package. In particular, when VBA macro function calls DLL, we can use this DLL indirectly by calling not DLL but its wrapper VBA macro. This is useful when you have difficulties in calling DLL directly in R for some reason. \(\blacksquare\)

To leave a comment for the author, please follow the link and comment on their blog: K & L Fintech Modeling.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.