CODEWITHSUNDEEP
rexcelvba
morg
morg

Reputation: 421

saveWorkbook function saves without macros

I am trying to use a VBA macro to call an R script to clean data based on variables in the excel workbook. I am having troubles pasting the cleaned data back into the excel workbook. I can use saveWorkbook to save it as an .xlsm file, however once I open the file, all my pre-set macros are gone, just disappeared. Is there a way to save the xlsm from R that keeps the macros intact?

As another note, is there a way to write this and have it update so that the file does not need to be closed and reopened? Is there a way to have it run in the same manner as a macro? 

wb <- loadWorkbook(file.xlsm) 
writeData(wb, sheet = "DataSet", data, startCol = 1, startRow = 2, colNames = FALSE) 
saveWorkbook(wb, file = file.xlsm, overwrite = TRUE)

It runs fine, but all the macros are gone. The file size goes from 806KB to 27KB

Upvotes: 1

Views: 221

Answers (1)

DecimalTurn
DecimalTurn

Reputation: 4129

An alternative solution would be to run your R script with VBA from another Excel file than the one your R script writes to.

You could then use a shell command that can wait for the script to finish before resuming to the rest of your VBA code (See this question for an example).

When the R script returns, your code could then do the remaining manipulations to the data exported by your R script. If you absolutely need the VBA code to be in that newly generated file, you could also add the VBA code modules in the file (see this question for an example) and save it as an xlsm file.

Upvotes: 1

Related Questions