[sims] Macros for excel - reporting

[jaky]

Hi

 

Has anyone got any information on macros in excel. Any help would be appreciated.

 

Thanks

 

Jackie

[saundersmatt]

What are you trying to achieve with said macros?

 

Matt

[pubgrub277]

Excel Help Forum is a useful link for asking excel related questions.

[jaky]

I want to create Report Templates in SIMS reporting. I know how to create macros in excel, I am a fairly competent VB progammer. But the information on attaching them to SIMS is non-existant. I believe there is a course called SIMS.net macro reporting. However, I feel that I can figure it out myself with a little help!

[Stuart_C]

Done it loads. Have a look on supportnet for the SIMS advanced reporting guide and it tells you how to create your own.

 

the process is rougly:

Find the SIMS excel template (Reportdata.xls, usually in a folder call "tempSimsRpt" in your home area).

Copy it somewhere normal.

Open a new excel workbook and set up your macro.

Copy the macro into your copy of the reportdata.xls (the advanced reporting guide will explain where to stick it)

Attach this new file as a custom template in SIMS.

Run report.

 

Personally I have found the fact I can't (at current) use xlsx files as templates to be frustrating. So personally I create a custom template for each report I and to produce in this way that copies all the data I've generated, opens an xlsx/xlsm file on the network and then pastes the data in. The file on the network then does all the work. It also make changing the thing easier as I don't nee to re-attach the template to the report every time I alter something.

[pubgrub277]

Stuart i do something similar with .Xml.

All my reports now produce xml be default and these in turn are interpreted by excel templates to produce the output/visual view i want.

 

The big advantage then is being able to use command line reporter to automate when the reports run.

[vikpaw]

Hi

 

Has anyone got any information on macros in excel. Any help would be appreciated.

 

Thanks

 

Jackie

 

This thread has what you need, you can probably ignore most of it, though there are some links to other threads / examples on supportnet. The document is attached. I think this is a newer version of the instructions floating around, but you should be able to work it out. http://www.edugeek.net/forums/mis-systems/38519-edit-excel-output.html#post359319

 

I recently used this with some reports i wanted to go out to Outlook, so was hiding the excel doc afterwards, which made editing and making changes really hard! I like the idea of dumping all to a interim document which does all the work.

[jaky]

Thanks I have worked it out now!