[sims] SIMS Reports - Excel Report Template

[mcrompton01]

Good morning,

 

I hoping someone will be able to help me as I've hit a brick wall write a report for SIMS. Basically I have created the report to export to Excel using a Report Template (Excel Macro).

 

I've amended the Auto_Open() macro and added a second sheet titled "lookups" to this template, for you've guessed it VLookups. However when I run the report the second sheets isn't copied or created on the final report.

 

Does anyone know how to do this? The other option is to programatcially add the new sheets and all its values, which can be done but is time consuming.

 

Any help greatly appreciated.

 

Mark

[MrMat]

Good morning,

 

I hoping someone will be able to help me as I've hit a brick wall write a report for SIMS. Basically I have created the report to export to Excel using a Report Template (Excel Macro).

 

I've amended the Auto_Open() macro and added a second sheet titled "lookups" to this template, for you've guessed it VLookups. However when I run the report the second sheets isn't copied or created on the final report.

 

Does anyone know how to do this? The other option is to programatcially add the new sheets and all its values, which can be done but is time consuming.

 

Any help greatly appreciated.

 

Mark

 

Where about in your Auto_Open macro have you entered your code? Most of the Auto_open macro actually runs against the ReportData spreadsheet which is closed at the end of the routine. Most probably you're adding a new worksheet to the wrong spreadsheet.

[MrMat]

This is how i would do it.

 

Save a copy of your ReportData.xls to desktop etc. Open it up and open the Auto_open Macro. Scroll to the very bottom and create a new macro; Sub Lookups or whatever.

 

Then scroll back up to the FixAddressColumn Function and call your new macro from there. That should do the trick!

 

[LosOjos]

This one caught me out at first too - when you run the report from SIMS, the macros will actually run on a new workbook created by the report template. The "lookups" sheet you have created won't be in that new workbook - so copy it over.

 

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

[mcrompton01]

Thanks LosOjos, where is the Auto_open macro should that be placed?

 

I've also found another soluntion which is to have the LookUps in a seperate file that can be accessed then run the following:

 

Const fromFile = "PATHTOFILEWITHLOOKUPS"
Dim srcBook As Workbook
Set srcBook = Application.Workbooks.Open(fromFile, UpdateLinks:=False, ReadOnly:=True, AddToMRU:=False)
srcBook.Sheets("LookUps").Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
srcBook.Close False

 

This way I can update the lookups as and when by editing this central file.

 

Mark

[LosOjos]

Thanks LosOjos, where is the Auto_open macro should that be placed?

 

I've also found another soluntion which is to have the LookUps in a seperate file that can be accessed then run the following:

 

Const fromFile = "PATHTOFILEWITHLOOKUPS"
Dim srcBook As Workbook
Set srcBook = Application.Workbooks.Open(fromFile, UpdateLinks:=False, ReadOnly:=True, AddToMRU:=False)
srcBook.Sheets("LookUps").Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
srcBook.Close False

 

This way I can update the lookups as and when by editing this central file.

 

Mark

 

Just put that line anywhere in your code before the point at which you try to reference the "lookups" sheet.

 

I'd be wary of looking up from an external source if you can help it - other people may not be able to run the report, you may not be able if you log in to another machine or if your NM or an OS update moves the user area for any reason... it can be done and is handy as it's easier to update, but generally I try to avoid doing that if it's a document I may want to share at some point.

[infamousbarfly]

This is how i would do it.

 

Save a copy of your ReportData.xls to desktop etc. Open it up and open the Auto_open Macro. Scroll to the very bottom and create a new macro; Sub Lookups or whatever.

 

Then scroll back up to the FixAddressColumn Function and call your new macro from there. That should do the trick!

 

 

Hi

 

Apologies for butting in here - I found this forum post via a google search - I wonder if you could answer me a question please? I'm currently trying to get my head around using SIMS reports with excel & macros, making slow but steady progress as I've never really used macros or vba before. Could you explain the reason why I have to call up my macro from the FixAddressColumn part of AutoRun? Why does it go there and not somewhere else?

 

Thanks

Sarah

[LosOjos]

Hi

 

Apologies for butting in here - I found this forum post via a google search - I wonder if you could answer me a question please? I'm currently trying to get my head around using SIMS reports with excel & macros, making slow but steady progress as I've never really used macros or vba before. Could you explain the reason why I have to call up my macro from the FixAddressColumn part of AutoRun? Why does it go there and not somewhere else?

 

Thanks

Sarah

 

Hi Sarah and welcome to Edugeek

 

Strictly speaking, your code doesn't have to go after the FixAddressColumn of the report - from a pure VBA stand point, you code could go anywhere in the Auto_Open() routine and still run when the sheet opens - Auto_Open() is a special routine in VBA that, if present, Excel will execute automatically upon opening the workbook.

 

However, the existing code in the default Excel template contains a lot of code to handle the formatting of your SIMS report (in fact, SIMS actually produces a CSV which this VBA code processes to turn in to a formatted Excel document).

 

So although strictly speaking your code doesn't have to go in that section, it's a good idea to put it there to ensure the worksheet you're expecting SIMS to produce is ready before you make any further alterations to it.

 

EDIT: just to clarify, if you run a SIMS report using the default portrait/landscape Excel output, these macros are actually run in a hidden Excel window to produce your report, so the worksheet you actually see is actually the result of these macros formatting. This is why you sometimes see an empty workbook flash on screen before your report, or a prompt to enable macros

Edited February 5, 2015 by LosOjos

[MrMat]

Hi Sarah and welcome to Edugeek

 

Strictly speaking, your code doesn't have to go after the FixAddressColumn of the report - from a pure VBA stand point, you code could go anywhere in the Auto_Open() routine and still run when the sheet opens - Auto_Open() is a special routine in VBA that, if present, Excel will execute automatically upon opening the workbook.

 

However, the existing code in the default Excel template contains a lot of code to handle the formatting of your SIMS report (in fact, SIMS actually produces a CSV which this VBA code processes to turn in to a formatted Excel document).

 

So although strictly speaking your code doesn't have to go in that section, it's a good idea to put it there to ensure the worksheet you're expecting SIMS to produce is ready before you make any further alterations to it.

 

EDIT: just to clarify, if you run a SIMS report using the default portrait/landscape Excel output, these macros are actually run in a hidden Excel window to produce your report, so the worksheet you actually see is actually the result of these macros formatting. This is why you sometimes see an empty workbook flash on screen before your report, or a prompt to enable macros

 

What he said ^!

[infamousbarfly]

Brilliant - thanks for this. I realised from looking through the code that a csv is produced and used as the basis for an excel sheet but I wondered if there was a specific reason for entering my macro in FixAddressColumn as I can't tell what it 'does' if you know what I mean. Having said that, what I know about vba syntax you could write on half a postage stamp so I can't tell what the majority of the AutoRun routine is doing!

 

The reason for my question really is that I have managed to achieve 99.9% of what I want - the final 0.1% that is stumping me is that I run my report from SIMS, the excel sheet opens and looks all fine but I receive "error 1004: Method 'Select' of object 'sheets' failed" and I cannot figure out why! If I place my macro at the end of the FixAddressColumn and run the report, the error does not appear but the formatting of the table that opens (as a result of my macro) is all incorrect. I was trying to figure out the purpose of FixAddressColumn, and the effect it has on the report output, but I haven't got very far as none of it makes much sense to me. I wondered if I could remove the whole FixAddressColumn and run my macro for elsewhere in the routine?

[LosOjos]

Brilliant - thanks for this. I realised from looking through the code that a csv is produced and used as the basis for an excel sheet but I wondered if there was a specific reason for entering my macro in FixAddressColumn as I can't tell what it 'does' if you know what I mean. Having said that, what I know about vba syntax you could write on half a postage stamp so I can't tell what the majority of the AutoRun routine is doing!

 

The reason for my question really is that I have managed to achieve 99.9% of what I want - the final 0.1% that is stumping me is that I run my report from SIMS, the excel sheet opens and looks all fine but I receive "error 1004: Method 'Select' of object 'sheets' failed" and I cannot figure out why! If I place my macro at the end of the FixAddressColumn and run the report, the error does not appear but the formatting of the table that opens (as a result of my macro) is all incorrect. I was trying to figure out the purpose of FixAddressColumn, and the effect it has on the report output, but I haven't got very far as none of it makes much sense to me. I wondered if I could remove the whole FixAddressColumn and run my macro for elsewhere in the routine?

 

To keep things readable, I generally put a call to my custom routine just after the page settings section of Auto_Open(). By that point in the code, the default macros havedone all of the important parts of their work.

 

As for Sheets.Select - are you trying to select an individual sheet? My guess is to make that the first thing the user sees? If so, make sure first of all you precede it with ActiveWorkbook (i.e. ActiveWorkbook.Sheets(x).Select) - this will ensure you're trying to select a sheet on the final workbook, not the book containing macros (as the macro book simply produces a new worksheet and closes, you can verify this by inspecting the VBA for the final workbook - there's no code!)

 

Don't rely on the sheet's ID to select it, as this will likely be different in the new workbook. Instead select it by name (ActiveWorkbook.Sheets("Sheet Name Here").Select).

 

Without seeing the actual code, it's tricky to help much more.

[infamousbarfly]

Hi again

 

As far as I'm aware, I haven't used a Sheets.Select command in my code. The thing that is baffling me is that if I call my routine from before or at the beginning of FixAddressColumn I get the error, but if I call my routine from the end of FixAddressColumn, without making any changes to it whatsoever, no error appears but the formatting is wrong. This leads me to assume that the error is not in my code, however I'm happy to be told otherwise if I can eliminate the error!

 

Can I send you my code for you to have a look at? I don't want to put you to any trouble so please feel free to say no if you don't have time.

 

Cheers

[LosOjos]

I'm happy to take a look for you, PM me if you don't want to post it here

 

Something that might help though is to comment out the "On Error GoTo ErrorHandler" line at the start of Auto_Open() - this overrides VBA's own "error handling" (i.e. throwing an error message and ceasing) but in so doing disables the debugger. If you comment that line out then when the error occurs, you can hit 'debug' in the resulting error message popup to see exactly where the problem is occurring to see if it sheds any light.

 

One gotcha - the default code in the template hides Excel at the beginning of execution, meaning it will be totally invisible in Windows. To show it again when debugging, type in the Immediate Window (CTRL+G to bring this up if you don't see it) "Application.Visible=True"

[MaggieMilligan]

Hi everyone

 

Just joined the site and really hope you can help me with a quick questions - I have used macros in the reports before but an now in a new school and I CANNOT find the reportdata.xls file anywhere! The reportdata.enc is in C:\Program Files (x86)\SIMS\SIMS .net but not the xls file, and there is no 'temp' folder inthere. Can you tel me where your reportdata.xls is sitting please

 

Thanks all

[MrMat]

Hi and welcome!

 

It should be located here: C:\Users\%USERNAME%\Documents\tempSimsRpt

[MaggieMilligan]

Thanks Mr Matt. That was where I expected to find it but curiously, the set up at this school has no 'documents' area let alone TempSimsRpt. Good to know that I am not going mad and that they should still go there

[Natashac15]

Hi please can anyone help me. I am new to producing reports through sims inexcel. Basically i want sims to run a report which already contains formulas and gives me the data with me having to filter. I need help with producing an interventions report which contains ppi info, ethnicity number ofinterventions, intervention comment i need all this data but with percentages already worked out when i run this report? Can soneone help? Also needhelp with another report which when i pressrun i want it to produce pivot table? Thank yoy

[LosOjos]

Hi @Natashac15

 

My best advice is to tackle it in stages - produce the base report from SIMS first, then record some macros to perform the actions you want.

Study the code the macros create in VBA Editor, you won't need to understand every single instruction but you'll need to at least get the gist of what each part does.

Once you're in a position where you can run your macro against the SIMS report manually and get the outcome you want, then move on to dropping your macros in to the SIMS Excel template.

 

There used to be a Capita SIMS document explaining how to get started with Excel templates, but I can't find it (then again, I can rarely find what I want on SupportNet; roll on the upgrade!). Does anyone know where you can get a copy of that document these days?

[vikpaw]

...snip...

There used to be a Capita SIMS document explaining how to get started with Excel templates, but I can't find it (then again, I can rarely find what I want on SupportNet; roll on the upgrade!). Does anyone know where you can get a copy of that document these days?

 

It's so elusive!

We have some useful info in this thread with ref to a forum thread on SupportNet but I don't think there was much there. More importantly @Natashac15 - the document most people start with is attached - hopefully this is a direct link:

http://www.edugeek.net/forums/mis-systems/38519-edit-excel-output.html#post359319

Edited February 10, 2015 by vikpaw
add link

[Natashac15]

thank you all for your comments, this is all really helpful, I am new to macros/VB, does anyone have a report template they could possibly send me that I can use that has formulas/macros already recorded by any chance, I also need the spreadsheet to automatically produce percentages for ethnicity vulnerable group attendance. Hope this makes sense. Thank you again

[LosOjos]

thank you all for your comments, this is all really helpful, I am new to macros/VB, does anyone have a report template they could possibly send me that I can use that has formulas/macros already recorded by any chance, I also need the spreadsheet to automatically produce percentages for ethnicity vulnerable group attendance. Hope this makes sense. Thank you again

 

It's not something that can really be explained in a few forum posts, they sell whole books on Macros & VBA!

 

Have a look at the links posted in this thread and work through some online VBA tutorials (Google throws this site up which looks good: Excel VBA Programming - a free course for complete beginners)

 

Like I said earlier, if you get it working in Excel first so you can just run your macro and it formats everything how you'd like, then the next step will be to incorporate that in to a SIMS template, which hopefully the other links in this thread will help with - or you can come back and ask again when you're at that point

[SouthCoastSaint]

Have you tried the new School Report? If you have applied the Autumn upgrade, this should give you the percentages you require for ethnicities. Reports > School report. If you tick attendance and show long version you get nice report with your vulnerabilities comparisons on it.

[Natashac15]

Thank you for the comments. I am currently just going over macros to familiarise myself with it all. I was wondering if any one can help me produce this sort of report please, so Behaviour weekly/half termly/term/year will calculate points, attendance will calculate percentage for each week, term/year etc. Thank you in advance.

 

Info	weekly	Half termly	term	year
Behaviour	/	/	/	/
Bullying	/	/	/	/
Attendance	/	/	/	/
Punctuality	/	/	/	/
DTs	/	/	/	/
Exits	/	/	/	/
Average ATL and residual +/-		/	/	/
Interventions	/	/	/	/

[MaggieMilligan]

Hi there

 

I am having the a similar issue with the "Error 1004", when the report runs it opens 2 spreadsheets, one with the word 'test' on the tab name and no data, the other with the data from the report - some reports return the data formatted, some don't - I created the report and on my computer anyone can log in and run the report without this error. On other people's computers even if I log in the report runs with this error message. It would appear older existing reports created before I arrived are also creating this error message now!

 

I have been unable to find the reportdata xls file at this new school ('documents' folder cant be located at the moment!) but our help desk sent me their file to use, so whether this is the issue (a faulty reportdata.xls file, and I am the only one with the correct one on my machine!) or whether it is a 'microsoft office' issue I have no idea.

 

Asked my ICT guys to look into it but would love to hear if anyone else is having similar issues

 

Thanks guys

[vikpaw]

An old or corrupted template file could do this. It's worth testing and differentiating between who is logged into the computer as well as which SIMS user is logged on.

This is because under one PC login, each SIMS user may get a different folder for sims.