well there is a way …
You could use the R package openxlsx to add a sheet to an existing Excel file with a macro. It is not super straightforward since you first have to check if the sheet is there, the delete it, save the file, open it again and then add a worksheet, then populate it and then write the whole thing again. Cf. this entry.
So numerous things could go wrong, but it is possible. Not sure how large the files can get. And I think the R package openxlsx is a little bit picky if you tell it to store an excel file with a macro and then there is no macro. So handle with care.
kn_example_excel_macros_xlsm.knwf (43.0 KB)