Using Excel Addins with RDCOM R package

Question

I want to use the RDCOMClient to run Excel from R and it works just fine, except that the add-ins, most importantly the "Solver" addin, won't work.

Does anyone know how to solve this issue?

Thanks in advance!

EDIT:

To prevent a real wall of text, here's a little bit of the code from the VBA and the R code. Notice the VBA code is a lot more complex but it runs fine in Excel. I can run other makros via RDCOM the way I do here. So that's why I guess the problem is the actual solver addin from Excel.

VBA:

Sub solve()
    SolverReset
    SolverOptions precision:=0.001
    SolverOk SetCell:="$I$44", MaxMinVal:=3, ValueOf:=0#,...

        SolverAdd CellRef:="$E$43", Relation:=1, FormulaText:="$E$32"
        SolverAdd CellRef:="$F$43:$G$43", Relation:=3, ...
        SolverAdd CellRef:="$H$43", Relation:=1, FormulaText:="$H$32"

    SolverSolve userfinish:=True

endsub

R:

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("...xlsb")
xlSht <- xlApp$Sheets("sheet")

xlApp$Run("solve")

Sorry, I edited the post and added the code. Hope it helps... — Ma Breidi, Jul 12 '17 at 17:10
Where in Excel workbook is this macro placed? Behind a sheet or standalone module? — Parfait, Jul 12 '17 at 18:04
It's in a module. So are all the other ones that have no trouble running. — Ma Breidi, Jul 12 '17 at 18:33
Is there any error you that emerges? Add-ins are user level configurations in the Excel application not inherent in any workbook. Does the user running R script have the Solver add-in installed in his/her Excel app? — Parfait, Jul 12 '17 at 20:55
No, there's no error or anything. Debugging didn't get me any further so far. I have the Solver add-in activated in the excel app as well as in VBA. — Ma Breidi, Jul 12 '17 at 21:20

score 0 · Answer 1 · answered Oct 01 '22 at 17:39

I have been able to do it with the following code :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlApp[["Visible"]] <- TRUE
xlWbk <- xlApp$Workbooks()$Open("D:\\test_Solver.xlsm")
xlApp$Run("Macro1")

The macro is

Sub Macro1()
SolverReset
SolverAdd CellRef:="$B$5:$B$6", Relation:=1, FormulaText:="4"
SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6"
SolverSolve True
End Sub

To make it work, you need to activate the solver in the developer's module (see https://peltiertech.com/Excel/SolverVBA.html). The code above did not work if I did not activate the solver in the developer's module. You have to click on "tool", then "reference" and then you activate the solver's add-in.

Using Excel Addins with RDCOM R package

1 Answers1