Getting error when run the VBA excel macro from R studio

[jpappire]

Hi,

I am trying to call VBA excel macros in R studio ,But unfortunatly am getting below error when trying to run the macro.As i need to schedule excel kind of report by calling VBA macros which exist in excel as part of report .

So kindly help me the right approach if this is not the correct way..

Sample code which am using :

install.packages("RDCOMClient", repos = "http://www.omegahat.net/R") 

library(RDCOMClient)
library(openxlsx)


# Open a specific workbook in Excel:
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("D:/Documents/vba22/Book2.xlsm")

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

# Run the macro called "MyMacro":
Sys.sleep(1)
xlApp$Run("Cyan")

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

# Release resources:
rm(xlWbk, xlApp)
gc()


Error :slight_smile: 
<checkErrorInfo> 80020009 
Error: Exception occurred.

[jdlong]

@jpappire

As i need to schedule excel kind of report by calling VBA macros which exist in excel as part of report .

There is no way we can guess what's going on inside your Excel macro. Can you make an example excel sheet and include that here too?

[jpappire]

Thank you very much for the reply,

I am unable to attach excel file due to permission issue from our organization.

The below macro code am using and output is coming fine as per macro when ran the macro within macro

Sub Cyan()

Cells.Interior.ColorIndex = 28
Set xlBook = xlApp.Workbooks.Open(pathfile, 0, True)
End Sub

[jdlong]

obviously don't attach proprietary work to a public discussion board, but you could make a simplified example (reprex) where you make a minimally reproducible example.