无法从VBA生成R Markdown报告。

huangapple go评论99阅读模式
英文:

Unable to generate R markdown report from VBA

问题

以下是您要翻译的部分:

I am trying to create a R markdown PDF document from VBA (don't get me started regarding why...). I am able to run R scripts from VBA so my idea was to create a R script and then use rmarkdown::render() from that script. Unfortunately this does not work for me and I cannot figure out why...

我正在尝试从VBA创建一个R markdown PDF文档(不要问我为什么这么做…)。我可以从VBA运行R脚本,所以我的想法是创建一个R脚本,然后从该脚本中使用 rmarkdown::render()。不幸的是,这对我来说不起作用,我无法弄清楚原因...

Everything works fine when sourcing the R script from within RStudio. That is, the report is created as it should.

当从RStudio内部调用R脚本时,一切都正常工作。也就是说,报告被创建得如预期。

If I comment out the rmarkdown::render() line in the RScript, then the R script is executed as it should when being called from VBA.

如果我在R脚本中注释掉 rmarkdown::render() 行,那么当从VBA调用时,R脚本将按预期执行。

I have spent quite some time on this so any help would be greatly appreciated.

我已经花了相当多的时间在这个问题上,因此非常感谢任何帮助。

In advance, thanks!
提前感谢!
C


The R markdown code for GenerateReportTest.Rmd:
GenerateReportTest.Rmd 的R markdown代码:

The code in the R script GenerateReportTest.R:
R脚本 GenerateReportTest.R 中的代码:

And finally, the VBA sub:
最后,VBA子程序:

英文:

I am trying to create a R markdown PDF document from VBA (don´t get me started regarding why...). I am able to run R scripts from VBA so my idea was to create a R script and then use rmarkdown::render() from that script. Unfortunately this does not work for me and I cannot figure out why...

Everything works fine when sourcing the R script from within RStudio. That is, the report is created as it should.

If I comment out the rmarkdown::render() line in the RScript, then the R script is executed as it should when being called from VBA.

I have spent quite some time on this so any help would be greatly appreciated.

In advance, thanks!
C


The R markdown code for GenerateReportTest.Rmd:

  1. ---
  2. title: "Untitled"
  3. author: "XXX"
  4. date: "6/15/2023"
  5. output: pdf_document
  6. ---
  7. ```{r setup, include=FALSE}
  8. knitr::opts_chunk$set(echo = TRUE)
  9. ```
  10. ## R Markdown
  11. This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see <http://rmarkdown.rstudio.com>.
  12. When you click the **Knit** button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:
  13. ```{r cars}
  14. summary(cars)
  15. ```
  16. ## Including Plots
  17. You can also embed plots, for example:
  18. ```{r pressure, echo=FALSE}
  19. plot(pressure)
  20. ```
  21. Note that the `echo = FALSE` parameter was added to the code chunk to prevent printing of the R code that generated the plot.

The code in the R script GenerateReportTest.R:

  1. print("1")
  2. Sys.sleep(5)
  3. rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"
  4. rmarkdown::render(rmarkdown_path)
  5. print("2")
  6. Sys.sleep(5)

And finally, the VBA sub:

  1. Sub RunRScript()
  2. Dim shell As Object: Set shell = VBA.CreateObject("WScript.Shell")
  3. Dim wait_till_complete As Boolean: wait_till_complete = True
  4. Dim style As Integer: style = 1
  5. Dim error_code As Integer
  6. Dim path_to_rscript As String
  7. path_to_rscript = "C:\...\GenerateReportTest.R"
  8. error_code = shell.Run("Rscript " & path_to_rscript, style, wait_till_complete)
  9. End Sub

答案1

得分: 1

以下是您要翻译的内容:

"I will answer this question myself...

The key (for me) was to figure the whole thing out was to add some error handling so that my R script code looked like:

  1. print("1")
  2. Sys.sleep(5)
  3. rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"
  4. tryCatch(
  5. expr = {
  6. rmarkdown::render(rmarkdown_path)
  7. },
  8. error = function(e){
  9. message('Caught an error!')
  10. print(e)
  11. },
  12. warning = function(w){
  13. message('Caught a warning!')
  14. print(w)
  15. },
  16. finally = {
  17. print("All done!")
  18. }
  19. )
  20. print("2")
  21. Sys.sleep(15)

I got the information that it had something to do with "pandoc" and after some searching I got everything to work by inserting Sys.setenv(RSTUDIO_PANDOC="C:\Program Files\RStudio\bin\pandoc") in the beginning of the script:

  1. Sys.setenv(RSTUDIO_PANDOC="C:\\Program Files\\RStudio\\bin\\pandoc")
  2. print("1")
  3. Sys.sleep(5)
  4. rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"
  5. tryCatch(
  6. expr = {
  7. rmarkdown::render(rmarkdown_path)
  8. },
  9. error = function(e){
  10. message('Caught an error!')
  11. print(e)
  12. },
  13. warning = function(w){
  14. message('Caught a warning!')
  15. print(w)
  16. },
  17. finally = {
  18. print("All done!")
  19. }
  20. )
  21. print("2")
  22. Sys.sleep(15)
  23. ```"
  24. <details>
  25. <summary>英文:</summary>
  26. I will answer this question myself...
  27. The key (for me) was to figure the whole thing out was to add some error handling so that my R script code looked like:
  28. ```r
  29. print(&quot;1&quot;)
  30. Sys.sleep(5)
  31. rmarkdown_path &lt;- &quot;C:/.../GenerateReportTest.Rmd&quot;
  32. tryCatch(
  33. expr = {
  34. rmarkdown::render(rmarkdown_path)
  35. },
  36. error = function(e){
  37. message(&#39;Caught an error!&#39;)
  38. print(e)
  39. },
  40. warning = function(w){
  41. message(&#39;Caught an warning!&#39;)
  42. print(w)
  43. },
  44. finally = {
  45. print(&quot;All done!&quot;)
  46. }
  47. )
  48. print(&quot;2&quot;)
  49. Sys.sleep(15)

I got the information that it had something to do with "pandoc" and after some searching I got everything to work by inserting Sys.setenv(RSTUDIO_PANDOC="C:\Program Files\RStudio\bin\pandoc") in the beginning of the script:

  1. Sys.setenv(RSTUDIO_PANDOC=&quot;C:\\Program Files\\RStudio\\bin\\pandoc&quot;)
  2. print(&quot;1&quot;)
  3. Sys.sleep(5)
  4. rmarkdown_path &lt;- &quot;C:/.../GenerateReportTest.Rmd&quot;
  5. tryCatch(
  6. expr = {
  7. rmarkdown::render(rmarkdown_path)
  8. },
  9. error = function(e){
  10. message(&#39;Caught an error!&#39;)
  11. print(e)
  12. },
  13. warning = function(w){
  14. message(&#39;Caught an warning!&#39;)
  15. print(w)
  16. },
  17. finally = {
  18. print(&quot;All done!&quot;)
  19. }
  20. )
  21. print(&quot;2&quot;)
  22. Sys.sleep(15)

huangapple
  • 本文由 发表于 2023年6月15日 21:03:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76482778.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定