Calling VBA macro from R

Asked Sep 22 '17 at 14:53

Active Sep 22 '17 at 17:25

Viewed 1,483 times

I'm trying to call a macro from R, and I have followed the answer to this question but I het an error:

<checkErrorInfo> 80020009 
Error: Exception occurred.

My code is the following:

library(RDCOMClient)

# Open a specific workbook in Excel:
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("Y:\\macroR.xlsm")

# this line of code might be necessary if you want to see your spreadsheet:
xlApp[['Visible']] <- FALSE 

# Run the macro called "MyMacro":
xlApp$Run("LoopAllExcelFilesInFolder")

# Close the workbook and quit the app:
xlWbk$Close(FALSE)
xlApp$Quit()

# Release resources:
rm(xlWbk, xlApp)

The macro is supposed to remove all the strikethrough cells in all the worksheets in all workbooks contained in a folder selected by the user. I can share the code if it is relevant.

edited Sep 22 '17 at 17:25

YowE3K

23,852
7
26
40

asked Sep 22 '17 at 14:53

Barbara

1,118
2
11
34

which line crashes ? – moodymudskipper Sep 22 '17 at 15:09
@Moody_Mudskipper it's the line xlApp$Run – Barbara Sep 22 '17 at 15:19
Have you tried to run it on an empty macro ? – moodymudskipper Sep 22 '17 at 15:23
1

My suspicion is that your xlApp isnt fully qualifying which workbook to look in for the macro it needs to fun. I dont know the code for it in R, but in VBA it would be something like `Application.Run(TargetWorkbook.FullName &"!"&"MacroName")`. – Brandon Barney Sep 22 '17 at 15:24
@BrandonBarney that was indeed the problem, the code in R is Workbookname.MacroName, where the workbook name is the one that you get in the macro view, so likely MyWorkbook. – Barbara Sep 22 '17 at 17:18
@Moody_Mudskipper no I had not tried that but the problem was actually the name without the reference to the workbook. – Barbara Sep 22 '17 at 17:19

Calling VBA macro from R

0 Answers0

Linked