MIS Systems Thread, SIMS Individual Reports Mail Merge Hiding Rows with Only Certain Empty Cells in Technical; Morning all! Another day, another challenge to overcome....
Is there any way in Individual Reports in AM that you can ...
10th January 2014, 11:35 AM #1
- Rep Power
SIMS Individual Reports Mail Merge Hiding Rows with Only Certain Empty Cells
Morning all! Another day, another challenge to overcome....
Is there any way in Individual Reports in AM that you can get the Word document to remove a row from a table if a certain cell is empty. I know you can if the row is empty but I need a subject with past grades to be excluded if the latest assessment point is not populated.
We include the historical grades for the previous assessment points of the year in all our reports but if the pupil has no result for the current assessment we want this row to be blank. The same problem is affecting targets all our pupils have targets for all subjects irrespective of whether they are taking them and of course, we do not want to display targets for subjects a student is not taking.
Any advice appreciated, cheers!
10th January 2014, 11:44 AM #2
You can do this with macros in the "ThisDocument" module of your template, but there is a caveat - if you upload the document to linked documents, VBA will throw an error if anyone tries to open it (unless they have macros disabled). The problem is that your template document references a file called IndRepBaseTemplate.dot, but when the file is opened via linked documents, that reference is missing which will cause VBA to throw a random unrelated error (very helpful!).
I opted to still use macros as the upload to SLG works fine and it makes printing from Individual Reports quicker for me.
I've put the code I use below as an example, but you will need to tweak it. In my template, there is a table used as a header (as headers/footers cause Ind Reps to run extremely slowly) followed by the table with the grades in. That table's final column is an effort grade, hence the check for the last but one column containing data.
Anyway, here's the code, let me know if you need more guidance on tweaking it for your needs:
Private Sub Document_Open()
On Error Resume Next
Dim x, y As Integer
With ActiveDocument.Tables(2).Columns(ActiveDocument.Tables(2).Columns.Count - 1)
y = .Cells.Count
'check we're not in template editing mode
If Left$(.Cells(2).Range.Text, 1) <> "<" Then
'delete rows containing no grade
For x = 2 To y
If x > y Then Exit For
If Asc(Left$(.Cells(x).Range.Text, 1)) < 32 Or _
Asc(Left$(.Cells(x).Range.Text, 1)) > 126 Then
x = x - 1
y = .Cells.Count
'fake document save to suppress warnings
ActiveDocument.Saved = True
Thanks to LosOjos from:
Xeba37 (10th January 2014)
10th January 2014, 11:57 AM #3
- Rep Power
The problem with that is that they open the reports in linked documents all the time and telling a hundred or so teachers to change their settings won't go well I can guarantee!
Interesting about the headers and footers though. I just tried removing them and my document seems to refuse to let me. I clearly have a lot to learn (I recently moved jobs from being Primary Assessment and Tracking LA Advisor to a Secondary Data Manager).
10th January 2014, 12:07 PM #4
You can't delete them as such, but as long as they're empty they're as good as gone. Now I must admit, I haven't tried headers/footers recently so there's a chance SIMS handles them fine now, I have so little time to experiment these days though I tend to stick to my tried and tested methods!
Originally Posted by Xeba37
Another alternative is to script the macro as VBS, so rather than embedding the code inside the document you could export the reports from SIMS, run them through the VBS to clean them up, then print them. Problem then though is that the linked documents version will still contain the rows you want removing!
13th January 2014, 01:20 PM #5
- Rep Power
Why not simply define your result tags by date? I.E Result for English between 01/10/2013 and 01/12/2013?
Have you followed the "things to remember when creating individual reports in Word 2010" helpsheet? Lots of little things to change and it really helps zap those Gremlins.....
By Xeba37 in forum MIS Systems
Last Post: 8th January 2014, 04:30 PM
By bwfc_nottingham in forum MIS Systems
Last Post: 6th January 2014, 10:05 PM
By tben2505 in forum MIS Systems
Last Post: 15th November 2011, 02:08 PM
By wesleyw in forum MIS Systems
Last Post: 19th October 2010, 10:48 AM
By LosOjos in forum MIS Systems
Last Post: 10th June 2010, 04:12 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)