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

No, nothing. I just can't open it when it's done. Excel stops, ans when I can finally open the file, nothing has changed in it (the macro didn't work). — Bambs
– Bambs, Commented Feb 3, 2016 at 11:21

Ekkehard.Horner · Accepted Answer · 2016-02-03 12:00:18Z

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.

edited Feb 3, 2016 at 12:00

answered Feb 3, 2016 at 11:39

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bambs Over a year ago

Thank you for your answer. pathfile is hardcoded in the vbscript. I changed it in the post so that you don't have the full path to read. I added On Error Resume Next but now the file is empty...

Bambs Over a year ago

Oh yes, I see what you mean now. I get "Unknown execution error" (translated from French)

Bambs · Accepted Answer · 2016-02-04 09:21:23Z

1

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.

answered Feb 4, 2016 at 9:21

Bambs

6455 silver badges15 bronze badges

Comments

AnalystCave.com · Accepted Answer · 2016-02-03 12:25:15Z

0

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.

answered Feb 3, 2016 at 12:25

AnalystCave.com

5,0242 gold badges25 silver badges31 bronze badges

1 Comment

Ekkehard.Horner Over a year ago

xlBook.Close SaveChanges:=False will cause an error in VBScript.

Collectives™ on Stack Overflow

Launch VBA macro from R

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related