SIMS.net Reporting - ReportData.xls and ExcelList.xls - what is actually going on?

[LouiseBlaize]

Hello all

 

I'm creating reports from SIMS using the process where you take a copy of ReportData.xls and add code to the AutoOpen macro to make it do fancy stuff... I'm ok with all that, when it works it does what I'm expecting (or if I've cocked up in the code, it fails in the way I'm expecting).

 

Where I'm struggling is that I don't really understand what is going on in terms of where ExcelList.xls comes into the equation! It causes me all sorts of trouble with sticking open when it shouldn't.

I'm also getting the problem where Excel will run - not get to the point where my code starts, and then closes without any errors or goodbyes! Sometimes that's fixed by a restart of SIMS, sometimes by a restart of the machine, sometimes by a cup of tea!

 

Does anyone have a clear explanation of what is actually going on? I appreciate that the report is kicking of the AutoOpen macro but I can't work out where ExcelList comes from, when it's created, how or why

 

Thanks

 

Lou

[blueday]

When you run an Excel report without a user template attached, it uses Reportdata.xls to create the output. If you create your own template by modifying the code in a copy of Reportdata.xls and then attaching that to the report, when you run the report it creates a copy of that template and calls it Excellist.xlsx and uses that instead of Reportdata.xls to create the output. With both methods the data that is output is stored in a text file called Reportdata.txt.

You can test your Excel template by putting it in a folder alongside the Reportdata.txt that has been output when running the report (make sure you take a copy immediately after running otherwise it may 'disappear') and then opening the template. This will also allow you to step through the code to see if something is causing it to error or shut down. In fact this method makes it much easier to modify and test the template without constantly having to keep attaching it to the report in SIMS and re-running it.

Edited March 10, 2023 by blueday

[a_masters]

You can test your Excel template by putting it in a folder alongside the Reportdata.txt that has been output when running the report (make sure you take a copy immediately after running otherwise it may 'disappear') and then opening the template. This will also allow you to step through the code to see if something is causing it to error or shut down. In fact this method makes it much easier to modify and test the template without constantly having to keep attaching it to the report in SIMS and re-running it.

 

Amazing - the amount of times I've had to tweak code in a template, change the template in the report, run it again, change some more code, edit the report again etc. Thank you!

[LouiseBlaize]

Thanks so much for this little life changing nugget!!!