从R启动VBA宏 [英] Launch VBA macro from R

问题描述

我目前正在尝试从R启动一个非常简单的VBA宏.为此，我遵循此处找到的过程:

I am currently trying to launch a very simple VBA macro from R. For that, I am following the procedure found here : Run VBA script from R Unfortunately, when I open the Excel file after that, it's corrupted and Excel stops. Here is my code :

r 的问题:

r:

library(XLConnect)
saveWorkbook(wb,pathfile)
# The saveWorkbook part is working
shell(shQuote(normalizePath(pathtovbs)), "cscript", flag = "//nologo")

---

vbscript 的问题:

---

vbscript:

Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 
  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook =     xlApp.Workbooks.Open(pathfile, 0, True) 
  xlApp.Run "PERSONAL.XLSB!MyMacro"
  xlApp.Quit 
  Set xlBook = Nothing 
  Set xlApp = Nothing 
End Sub

---

vba PESONAL.XLSB！ MyMacro:

---

vba PESONAL.XLSB!MyMacro:

Sub MyMacro()

 Dim ws As Worksheet
    For Each ws In Sheets
       ws.Range("C:C").EntireColumn.Delete
       ws.Range("A:A").EntireColumn.Delete
   Next ws
End Sub

您知道发生了什么吗?我检查了每个文件的路径，它们很好. 提前非常感谢您.

Do you have any idea what is going on ? I have checked the path of each file and they are good. Thank you very much in advance.

显然，问题来自vbscript.该文件打开，但找不到我的个人库(PERSONAL.XLSB)中的宏.手动打开Excel时，可以访问此宏，但是从其他程序打开Excel时，则不能.知道为什么吗?

Edit : Apparently, problem comes from the vbscript. The file opens but it can't find the macro located in my personal library (PERSONAL.XLSB). When I open Excel manually, I can access this macro, but when I open Excel from another program, I can't. Any idea why ?

Pauline

推荐答案

我找到了解决方案.如果你们中的某些人试图从vbscript调用个人库，则需要添加:

I found the solution. If some of you are trying to call personnal library from a vbscript, you need to add :

Dim objWorkbook
Set objWorkbook=xlApp.Workbooks.Open("D:\users\...\PERSONAL.XLSB")

在启动宏之前.

这篇关于从R启动VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！