CODEWITHSUNDEEP
rexcelvbardcomclient
Frank
Frank

Reputation: 1216

import and run excel module .bas with RDCOMClient

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

Upvotes: 1

Views: 508

Answers (1)

Parfait
Parfait

Reputation: 107767

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

Upvotes: 1

Related Questions