Advanced Reports with Excel

[Lydia]

If I try my own custom Excel template, SIMS does not put any data into Excel, so I have used SIMS basic template and can now see the exported data and run macro's. My problem is I am unable to see the new worksheets I have added into the basic template, it always shows the original worksheets created in the basic template.

 

I have about 10 worksheets I want to include with macros. How can I add more worksheets?

[LosOjos]

If I try my own custom Excel template, SIMS does not put any data into Excel, so I have used SIMS basic template and can now see the exported data and run macro's. My problem is I am unable to see the new worksheets I have added into the basic template, it always shows the original worksheets created in the basic template.

 

I have about 10 worksheets I want to include with macros. How can I add more worksheets?

 

When SIMS opens a report in Excel, it runs through a set of macros to format the output. It is essential that these macros are included, which iswhy it's always best to base your own templates on one of the existing SIMS ones (the reports are actually output as CSV, then the default macros Capita have created paste the data in to Excel.)

 

The reason you're not seeing your new sheets in the final report is that the template puts all ofthe data in to a new workbook, not it's own. Simple solution: copy any sheets you want from your template to the new one with a line of code something like this:

 

ThisWorkbook.Sheets.Copy Before:=ActiveWorkbook.Sheets(1)

 

This takes all of the sheets in your template and copies them to the start of the workbook that the macros are populating

[Lydia]

I had to find the right location within my original code, and it works a treat.

 

Thank you.