import and run excel module .bas with RDCOMClient

Question

I'm trying to use RDCOMClient to import a module to an excel workbook.

xlApp <- COMCreate("Excel.Application")
XlWbk <- xlApp$Workbooks()$Open(paste0(root, "test.xlsx"))
xlWbk.VBProject.VBComponents.Import(paste0(root, "macro/Module1.bas"))

the last line gives the error:

 could not find function "xlWbk.VBProject.VBComponents.Import"

I also tried the following:

xlApp$Run(paste0(root, "macro/Module1.bas!header"))
xlApp$Modules()$AddFromFile(paste0(root, "macro/Module1.bas"))

Both of the above code also did not work.

Can anyone help me import this module? I generate this excel macro with R, and it's a real pain to copy and paste them into the excel document and run macro by hand.

edit---------

here is the content of the .bas

Attribute VB_Name = "Module1"
Sub Macro2()
Attribute Macro2.VB_ProcData.VB_Invoke_Func = " \n14"
'
' Macro2 Macro
'
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "hello"
    Range("I7").Select
End Sub

Parfait · Accepted Answer · 2020-06-05T18:29:25.963

1

R does not use period qualifiers to access underlying methods. However, periods are allowed in object names. So R is attempting to find a function named xlWbk.VBProject.VBComponents.Import().

While in VBA or VBS, period means accessing properties and attributes, in R you need to adjust with [[ or $ qualifiers. Hence, consider adjusting to R semantics:

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(paste0(root, "test.xlsx"))

xlWbk$VBProject()$VBComponents()$Import(paste0(root, "macro/Module1.bas"))

The counterpart in VBA would be using period qualifiers for methods:

Public Sub ImportModuleCheck()
On Error Goto ErrHandle
   ThisWorkbook.VBProject.VBComponents.Import "C:\Path\To\macro\Module1.bas"

   Msgbox "Module successfully imported!", vbInformation
   Exit Sub

ErrHandle:
   Msgbox Err.Number & " - " & Err.Description, vbCritical
   Exit Sub
End Sub

edited Jun 05 '20 at 18:29

answered Jun 03 '20 at 21:27

Parfait

104,375
17
94
125

Thanks so much for your reply! I'm getting an error with no tips as to why it caused an error: ```xlWbk$VBProject()$VBComponents()$Import(paste0(root, "macro/Module1.bas")) Error: Exception occurred.``` I added info to the op. – Frank Jun 03 '20 at 22:12
1

What is the content of *root*? Is the `.bas` file located in sub folder, *macro*? Please note you use upper case `X` in workbook object. I use lower case`x` in `xlWbk` to be consistent with `xlApp`. – Parfait Jun 03 '20 at 23:57
Thanks for reply. root is the path to a folder which contains the root folder, and the file module1 above is in root/marco/. I know the file path is fine because ```readLines(con = paste0(root, "macro/Module1.bas"))``` works. I exactly copy paste your code above also, and I keep getting "an exception occurred". – Frank Jun 04 '20 at 16:50
I just imported the file manually through the editor in excel and it worked there. So the code in the file is good also. – Frank Jun 04 '20 at 16:55
1

Hmmm...what version of MS Office are you running? Are you on Windows or Mac? I thought at first it was the `.xlsx` which cannot hold any macros (but `.xlsm` can) was the issue but attempting even that you should be prompted with the background Excel process. How are you running R script? Command line, IDE like RStudio, other? – Parfait Jun 04 '20 at 17:09
Thank you again for helping, I really appretiate it. ```Version - Microsoft Office Professional Plus 2013```, I am running code through RStudio. I am on Windows. – Frank Jun 04 '20 at 18:44
You mean manually in excel? Where in Excel would I put that line? I tried to run it in VBA editor in Excel alt+F11, but it didn't do anything. – Frank Jun 05 '20 at 17:14
1

See extension of answer showing the VBA counterpart. Run that macro (adjusting path) and describe if code worked or erred out and if erred out what is the exact message. – Parfait Jun 05 '20 at 18:30
got this error - ```1004 - Programmatic access to visual basic project is not trusted``` – Frank Jun 05 '20 at 21:59
1

Well there you have it! That should be your issue. [Adjust trust settings](https://support.microsoft.com/en-us/help/282830/programmatic-access-to-office-vba-project-is-denied) of Excel file or maybe move working directories. In fact, with this error you should not be able to run any VBA macro (i.e., whatever you attempt to import). – Parfait Jun 05 '20 at 22:37
Man, you are the best. Do you know if it is possible to adjust those settings programmatically? – Frank Jun 05 '20 at 22:57
Glad it finally worked out. There might be an automated way but don't know immediately. Look it up and see. – Parfait Jun 06 '20 at 00:04

import and run excel module .bas with RDCOMClient

1 Answers1