0

I am trying to automate some of my tests in R to produce a static report in Excel. I have created a template in Excel which has a few charts and tables(sheet 1). Now I run my R code to generate the data to fill in the same excel template file on Sheet 2.

I am using Openxlsx package to loadworkbook(excel template), next I overwrite data in sheet 2 by deleting the sheet and recreating it again with the new data so that the excel template has data for new test runs.

This runs without any error. But when I open my excel back the charts disappear with the !REF# error whereas as the tables are overwritten properly in the template(sheet1).

Has anyone come across such a scenario? The method I am using is a bit weird but can't think of any other alternative.

Thanks in advance!!

R trader
  • 31
  • 1
  • 5

1 Answers1

0

This definitely sounds weird. Something seems off, but I'm sorry I can't tell you what the issue may be. Anyway, I would say, just use R to generate the data and dump everything into Excel. Then, run some VBA in Excel to create the charts. I have no idea what your VBA skills are like, but I'm guessing it would be much easier to crate charts in Excel using VBA, rather than trying to do all of this with R.

Here are a few resources that you may find useful.

https://www.thespreadsheetguru.com/blog/2015/3/1/the-vba-coding-guide-for-excel-charts-graph

https://analysistabs.com/excel-vba/chart-examples-tutorials/

http://www.sthda.com/english/wiki/r-xlsx-package-a-quick-start-guide-to-manipulate-excel-files-in-r

Finally, you can learn a lot by recording Macros and hitting F8 to step-through the code to see how everything works.

ASH
  • 20,759
  • 19
  • 87
  • 200