Is there any way out to Embed R code within Excel?

Question

When we need automation we use to write macro code in excel. Instead of writing the VBA codes can we write R code within Excel. ?

I tried searching in google but couldn't found material upon it.

Answer 1

Yes, you will have to pass by VB Script but you can do it by following these steps:

1. Write VB Script and Save it as .vbs file

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts=False
Set wb = objExcel.Workbooks.Open("C:\Users\Deepanshu\Documents\example.xlsx")
Set Xlsheet = wb.Worksheets("PRDSALE")
Xlsheet.UsedRange.Borders.LineStyle = xlContinuous
Xlsheet.UsedRange.Borders.Color = RGB(0, 0, 0)
Xlsheet.UsedRange.Borders.Weight = xlThick
wb.save

2. Run the following code in R
   
3. Run Excel Macro from R

pathofvbscript = "C:\\Users\\Deepanshu\\Documents\\border.vbs"
shell(shQuote(normalizePath(pathofvbscript)), "cscript", flag = "//nologo")