1

As one of my clients wants to use a fully automated excel/tm1 solution to work with an integrated R forecast procedure, I am searching a way for users to inspect separately the original time series, the time series after data normalization and the time series plus forecast horizon and so on.

To run one of the app solutions, I want to use a vba-button to trigger a shiny-app for time series graphical output script. if I opened the app in Rstudio, a vba-button opening the browser with the given address opens and the given app output appears. However, I do not know how to do this without opening Rstudio GUI. The shiny app should be able to run in the background somehow.

Is there any comprehensible and practical solution out there?

Leonhard Geisler
  • 506
  • 3
  • 15
  • You can run an instance of RStudio Server somewhere. This is RStudio in the browser. There is also shinyserver to host shiny apps independently of RStudio, which is not required in a production setup. – danlooo Nov 25 '21 at 15:13
  • I might have found the solution: runApp(list(ui = ui, server = server),host="192.168.xx.xx",port=5013, launch.browser = TRUE). If I paste this code into my app instead of shinyapp(...), the browser is opening when I trigger the script via batch file from vba-button – Leonhard Geisler Nov 25 '21 at 15:17
  • 1
    Yes, any R session having the package shiny installed can host shinyapps locally. Keep in mind that this is a local deployment. If multiple users want to access the app, you might need to add load balancers etc. – danlooo Nov 25 '21 at 15:21
  • I will keep that in mind. Luckily for know it is just for a single user locally – Leonhard Geisler Nov 25 '21 at 15:25
  • Also see [this](https://stackoverflow.com/questions/26799722/hosting-and-setting-up-own-shiny-apps-without-shiny-server) related post. – ismirsehregal Nov 25 '21 at 18:13
  • @LeonhardGeisler, please answer your own question with that batch solution. Specifically, show the batch file commands and VBA macro. Future readers will be interested. – Parfait Nov 25 '21 at 18:27

1 Answers1

1

Since you can run Shiny apps from command line (outside of RStudio), consider having VBA call the command with WScript.Shell (a popular command line caller) or use VBA's Shell.

CMD

Using cmd.exe, below launches a secondary window, assuming you are running Excel from a Windows environment and have Rscript directory in PATH environment variable. Also, comamnd runs runApp outside of R code, expecting shinyApp(...) call in code.

cmd.exe /k Rscript -e "library(shiny); runApp('/path/to/app/directory_or_script')"
  • For Mac: replace cmd.exe /k with open -a
  • For Linux: replace cmd.exe /k with gnome-terminal -x

VBA

Sub Run_R_Shiny()
On Error Goto ErrHandle
    Dim shell As Object
    Dim errorCode As Integer
    Dim rCommand As String

    rCommand = "cmd.exe /k Rscript -e ""library(shiny); runApp('/path/to/app/directory_or_script')"""
    Set shell = VBA.CreateObject("WScript.Shell")
    errorCode = shell.Run( _
        strCommand:=rCommand, _
        intWindowStyle:=1, _
        bWaitOnReturn:=False _
    )

ExitHandle:
    Set shell = Nothing                               ' RELEASE ALL set OBJECTS
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandle
End Sub

By the way, you can use this same approach to run R scripts with the automated Rscript executable.

Parfait
  • 104,375
  • 17
  • 94
  • 125