Launch VBA macro from R

Question

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:

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

---

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:

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.

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

Answer 1

pathfile is not defined in your .vbs:

Set xlBook =     xlApp.Workbooks.Open(pathfile, 0, True)

and your EVIL

On Error Resume Next

hides all errors.

Update wrt comment:

("I added On Error Resume Next but now the file is empty") - You already have an EVIL OERN; you should remove it - and then study the error message - probably concerning the parameters to the .Open call.

Answer 2

You are doing xlApp.Quit without saving and closing the Workbook first.

Add this:

xlBook.Save
xlBook.Close SaveChanges:=False

Before:

xlApp.Quit

And remove On Error Resume Next to see errors.

Answer 3

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")

before launching the macro.