Here is my excel VBA code for exchanging data with R. It starts an R script and then reads back a txt file with numerical results and three plots as svg images.
Sub RunRscript2()
Dim shell As Object: Set shell = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Long
Dim sFile As String
'Cleanup
ThisWorkbook.Save
Worksheets("Sheet1").Activate
On Error Resume Next
Worksheets("Sheet1").QueryTables("output1").Delete
On Error GoTo 0
Worksheets("Sheet1").Pictures.Delete
'=========
sFile = ThisWorkbook.Path & "\test_xls_connectivity.R"
errorCode = shell.Run("C:\R\R-3.3.0\bin\x64\Rscript.exe " & sFile & "", windowStyle, waitOnReturn)
'errorCode = shell.Run("Rscript.exe " & sFile & "", windowStyle, waitOnReturn)
Worksheets("Sheet1").Range("$F$3").Select
sFile = ThisWorkbook.Path & "\plots.png"
ActiveSheet.Pictures.Insert(sFile).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With
Worksheets("Sheet1").Range("$F$30").Select
sFile2 = ThisWorkbook.Path & "\plots2.png"
ActiveSheet.Pictures.Insert(sFile2).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With
Worksheets("Sheet1").Range("$N$3").Select
sFile3 = ThisWorkbook.Path & "\plots3.png"
ActiveSheet.Pictures.Insert(sFile3).Select
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With
'''''''''''''''''''''''''
strFile = ThisWorkbook.Path & "\output.csv"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strFile & "", Destination:=Range("$N$30"))
.Name = "output1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
And here is my R file which reads data from excel and then returns plots as images and reports in text format that VBA will paste in an Excel sheet. The commented install.packages need to be run once in R (maybe it needs to be corrected so that if the packages are not installed R should install them). Note the args... and then scriptPath... piece of the code which reads the path to the current location of the script. Not my solution - I will attach the source soon, sorry and thanks to the original author.
# install.packages("xlsx")
# install.packages("rattle")
# install.packages("RColorBrewer")
# install.packages("rpart.plot")
# install.packages("scatterplot3d")
require(xlsx)
require(rpart)
# require(rattle)
require(rpart.plot)
require(ggplot2)
require(scatterplot3d)
rm(list=ls())
args <- commandArgs(trailingOnly = F)
scriptPath <- normalizePath(dirname(sub("^--file=", "", args[grep("^--file=", args)])))
setwd(scriptPath)
# setwd("C:\\Users\\jkotows2\\Desktop\\testR_xlsm")
#read from Sheet1
DaneBadawcze = read.xlsx("test_R.xlsm",1, encoding = "UTF-8", stringsAsFactors=T)
attach(DaneBadawcze)
fit <- rpart(Padało ~ Temperatura + Ciśnienie + Wilgotność,
,data=DaneBadawcze, method= "class")
#cluster<-cutree(hclust(dist(DaneBadawcze[,names(DaneBadawcze)!="Padało"]) ),k = 4)
cluster<-cutree(hclust(dist(DaneBadawcze) ),k = 4)
DaneBadawcze$Klaster<-cluster
#print a plot to a file
fn <- "plots.png"
if (file.exists(fn)) file.remove(fn)
png(fn)
#fancyRpartPlot(fit)
prp(fit,type = 2, fallen.leaves = T, extra = 3, compress = T)
dev.off()
#print another plot to a file
fn <- "plots2.png"
if (file.exists(fn)) file.remove(fn)
png(fn)
scatterplot3d(type="h", x=Temperatura, y=Ciśnienie, z=Wilgotność,color=c("green","blue") [DaneBadawcze$Padało] )
dev.off()
#print another plot to a file
fn <- "plots3.png"
if (file.exists(fn)) file.remove(fn)
png(fn)
scatterplot3d(type="h", x=Temperatura, y=Ciśnienie, z=Wilgotność,color=c("green","blue","red","brown","orange") [DaneBadawcze$Klaster] )
dev.off()
write.csv2(fit$variable.importance, file="output.csv", row.names = T)
#print a report to a file
sink(file="report.txt", type="output")
fit$variable.importance
unlink("report.txt")
sink()
To be continued.
