5

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 :

:

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

:

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

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

Community
  • 1
  • 1
Bambs
  • 545
  • 4
  • 14
  • Do you get any error messages? – tospig Feb 03 '16 at 11:10
  • 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 Feb 03 '16 at 11:21

3 Answers3

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.

Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • 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 Feb 03 '16 at 11:47
  • Oh yes, I see what you mean now. I get "Unknown execution error" (translated from French) – Bambs Feb 03 '16 at 14:38
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.

Bambs
  • 545
  • 4
  • 14
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.

AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30