问题

以下是您要翻译的部分：

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子程序：

---
title: "Untitled"
author: "XXX"
date: "6/15/2023"
output: pdf_document
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

## R Markdown

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>.

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:

```{r cars}
summary(cars)
```

## Including Plots

You can also embed plots, for example:

```{r pressure, echo=FALSE}
plot(pressure)
```

Note that the `echo = FALSE` parameter was added to the code chunk to prevent printing of the R code that generated the plot.

print("1")
Sys.sleep(5)

rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"

rmarkdown::render(rmarkdown_path)

print("2")
Sys.sleep(5)

Sub RunRScript()

    Dim shell As Object: Set shell = VBA.CreateObject("WScript.Shell")
    Dim wait_till_complete As Boolean: wait_till_complete = True
    Dim style As Integer: style = 1
    Dim error_code As Integer
    Dim path_to_rscript As String

    path_to_rscript = "C:\...\GenerateReportTest.R"
    
    error_code = shell.Run("Rscript " & path_to_rscript, style, wait_till_complete)

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:

print("1")
Sys.sleep(5)

rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"

tryCatch(
  expr = {
    rmarkdown::render(rmarkdown_path)
  },
  error = function(e){
    message('Caught an error!')
    print(e)
  },
  warning = function(w){
    message('Caught a warning!')
    print(w)
  },
  finally = {
    print("All done!")
  }
)

print("2")
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:

Sys.setenv(RSTUDIO_PANDOC="C:\\Program Files\\RStudio\\bin\\pandoc")

print("1")
Sys.sleep(5)

rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"

tryCatch(
  expr = {
    rmarkdown::render(rmarkdown_path)
  },
  error = function(e){
    message('Caught an error!')
    print(e)
  },
  warning = function(w){
    message('Caught a warning!')
    print(w)
  },
  finally = {
    print("All done!")
  }
)

print("2")
Sys.sleep(15)
```"

<details>
<summary>英文:</summary>

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:

```r
print(&quot;1&quot;)
Sys.sleep(5)

rmarkdown_path &lt;- &quot;C:/.../GenerateReportTest.Rmd&quot;

tryCatch(
  expr = {
    rmarkdown::render(rmarkdown_path)
  },
  error = function(e){
    message(&#39;Caught an error!&#39;)
    print(e)
  },
  warning = function(w){
    message(&#39;Caught an warning!&#39;)
    print(w)
  },
  finally = {
    print(&quot;All done!&quot;)
  }
)

print(&quot;2&quot;)
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:

Sys.setenv(RSTUDIO_PANDOC="C:\\Program Files\\RStudio\\bin\\pandoc")

print("1")
Sys.sleep(5)

rmarkdown_path <- "C:/.../GenerateReportTest.Rmd"

tryCatch(
  expr = {
    rmarkdown::render(rmarkdown_path)
  },
  error = function(e){
    message('Caught an error!')
    print(e)
  },
  warning = function(w){
    message('Caught an warning!')
    print(w)
  },
  finally = {
    print("All done!")
  }
)

print("2")
Sys.sleep(15)