VBA to run R script with an output in Excel

Question

I am trying to work with R within excel.I have written a R script which works well in R studio-The final output is a excel file.Can someone please help me with a VBA code? I have tried several sources but without any success(Link1),Link2,Link3. This is a very simple R code I want to run from VBA(I need output within excel sheet or as a new excel file-as it does in R).

url<-("https://www.w3schools.com/xml/plant_catalog.xml")
data_df<- xmlToDataFrame(url)
library(xlsx)
write.xlsx(data_df,"P.xlsx")

Answer 1

Yeah, that's sounds right, when R Studio starts up you may need to choose between 32-bit and 64-bit, based on what version of Excel you are running.

https://sites.google.com/site/rforfishandwildlifegrads/home/week_2/default32bit

Now, to run an R script from Excel, do something like this.

Sub RunRscript1()
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorCode As Integer
    Dim path As String

    ' path to R executable:  C:\Users\rshuell001\Documents\R\R-3.2.5\bin\x64\R.exe
    ' path to R script:  C:\Users\rshuell001\Documents\R\Download.r
    ' see more setup details here
    ' http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html
    path = "C:\Users\rshuell001\Documents\R\R-3.2.5\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\rshuell001\Documents\R\Download.r"
    'path = """C:\Users\rshuell001\Documents\R\R-3.2.5\bin\i386"" C:\Users\rshuell001\Documents\R\Download.R"
    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

That scenario works fine for me. Just modify it slightly to suit your specific needs.