1

I am using RDCOMClient package in R. I am able to run existing Macro in Excel Workbook from R. Is there a simple way to add Macro to Excel Workbook, when Macro is written in simple ".txt" file?

So far I have:

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(normalizePath(list.files(pattern=".xlsm")[1])) 
### open first xlsm file in working directory
xlApp$Run("Send") ## Run existing Macro named "Send"

can i do something like xlApp$AddMacro("myMacro.txt") to add new Macro to the workbook?

Thanks.

EDIT: I manage to add empty module and name it, but somehow I cannot add actually code to it:

mo <- xlApp$Modules()$Add()
mo[["Name"]] <- "Test"
smth like mo[["Body??"]]
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
JacobJacox
  • 917
  • 5
  • 14
  • Covered in depth here: http://www.cpearson.com/excel/vbe.aspx Should be adaptable to R/COM – Tim Williams Sep 07 '18 at 07:05
  • Can you help me insert the code to the module? I went through procedure desribed on the page but it doesnt seem to wrok. @TimWilliams – JacobJacox Sep 07 '18 at 08:43
  • It would be much easier to export the module containing the macro from Excel as a *.bas file, and then import that. – Tim Williams Sep 07 '18 at 15:14
  • ...from the link I posted: `ToVBProject.VBComponents.Import Filename:=FName` – Tim Williams Sep 07 '18 at 17:16
  • I also would like an answer to this question. I have R write a macro, and if I add that macro manually to a master macro workbook, I can open both the master macro workbook, and the workbook I need edited. Then I can use the macros. But I need a way to have R add the macros to the master workbook. The document you linked is really long, made for a different programming language, and difficult to understand. – Frank Jun 03 '20 at 18:10
  • @Frank Probably best to open new question, this one is old an noone is looking into it anymore. – JacobJacox Jun 07 '20 at 20:43

0 Answers0