0

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")
Improve this question
2
  • what have you tried ? lookup RExcel also Commented Mar 24, 2018 at 13:56
  • It just works on Excel 32 bit not 64 bit. Commented Mar 24, 2018 at 14:09

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.