Run VBA macro from R

Question

I'm currently trying to run a very simple VBA macro from R. To do this, I follow the procedure found here: Run the VBA script from R Unfortunately, when I open the Excel file after that, it is 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 checked the path of each file and they are good. Thank you so much in advance.

Edit: Apparently, the problem comes from vbscript. The file opens, but the macro was not found 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 cannot. Any idea why?

Pauline

Answer 1

I have found a solution. If some of you try to call a personal library from vbscript, you need to add:

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

before running the macro.

Answer 2

pathfile not defined in your .vbs:

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

and your evil

 On Error Resume Next 

hides all errors.

Update comment:

("I added" Turning on the error "Next, but now this file is empty"). You already have the SOUND NUT; you should delete it and then examine the error message - perhaps regarding the options for calling .Open .

Answer 3

You execute xlApp.Quit without saving and closing the book in the first place.

Add this:

 xlBook.Save xlBook.Close SaveChanges:=False 

Before:

 xlApp.Quit 

And delete On Error Resume Next to see the errors.