This is a question where both R and VBA is used. I have made a R script which creates a data frame with some specific data I need. Then I use "openxlsx" to export this data frame to an existing workbook where I have made some VBA code and linked an ActiveX box to this VBA code. However, the problem is that everytime the R script is run the ActiveX box does not work anymore (i.e. it is no longer linked to the macro I have made in VBA and, therefore, I am not able to click on it anymore). Has anyone experienced this before and know a way how to solve this?

I think the issue is that everytime the R script is run then under "Devloper" where the R script apparently adds another Sheet as shown in the down below picture

and therefore the macros I linked to the ActiveX box is thus deleted.

I know a possible way is to insert the data to another excel file and then have the macro in another excel file as well but is this really the only way to fix this problem?

TIA.

CodePudding user response：

XLSX file format doesn't support macros or active content. Your openxlsx library is likely not macro aware so, naturally, they don't work anymore.

Port all of your code to VBA and just run it from Excel. It will be much easier than trying to do things from R.

If you need to do data processing in R, that's fine. Just perform the import from Excel. You can even open Excel from R and run the VBA macro so there is a clean handoff where your math is done in R and all Excel functions are performed natively.

Use the correct tool for the job.

There is already an issue for this problem logged in the openxlsx library here: https://github.com/ycphs/openxlsx/issues/44