MIS Systems Thread, Getting a Word VB Macro to execute when sending Individual Report to document server in Technical; Hi
I have a VB Script that does some colouring in on an Individual Report, which is more complex than ...
23rd February 2012, 12:39 PM #1
Getting a Word VB Macro to execute when sending Individual Report to document server
I have a VB Script that does some colouring in on an Individual Report, which is more complex than the colouring that SIMS can do. When I set up the Individual Report template, I put the code into the Private Sub Document_Open() bit.
This works when you run the report for preview or print, because word actually executes and the macro runs. However if you send the report to the document management server to store with the pupil's record, then the code never runs. If a user opens that file, the code will run then, but that's dependent on having their macro settings correct, which you can't guarantee.
Is there any way of getting around this? Does Word execute at all when you save an individual report or does SIMS do all the XML merging itself? If it does run, is there another Sub that I can put the code into?
If I go down the road of PDFing files (I see there are some threads on this), is there an easy way of Bulk Uploading to the student record? The file name by default has the student name and admissions number on it so it should be possible?
IDG Tech News
23rd February 2012, 01:25 PM #2
I stopped using Macros in word documents for this reason, but if someone knows of a way I would grateful.
23rd February 2012, 02:09 PM #3
I have a VB macro in some of our individaul reports to do colour coding and as long as the member of staff accessing the linked document has their security settings to at least prompt them to enable macros the code runs. Staff all know that they should enable macros when looking at such documents.
23rd February 2012, 03:38 PM #4
Could you sign your macros? Then have gpo to allow signed macros?
26th February 2012, 02:15 PM #5
Hi @eean - I had a macro built into the sims template to fiddle with a font, but found it didn't work on generation.
Originally Posted by eean
Did you add your own macro to the indrepbasetemplate.dot or just into the template you create through sims? I'd be interested to hear how you've done it, see an example. I can't find the document_open() sub. When i last looked to try something, all the built-in macros were password protected, so i guess you add a new one.
I heard on SupportNet that it's possible to get a macro to work inherently by putting it in the autorun() - i've been meaning to test this out but not had the time.
As far as i can tell, Word does get opened during the upload however, it's hidden from display, and for whatever reason, the document_open() isn't activated.
There is no easy way to bulk upload documents to the DMS. At the moment we do a load manually. There are some tools like RecordLink Writer which make it very easy, and it's not that expensive, but it's still a manual (one student at a time) process as far as i understand.
6th March 2012, 02:34 PM #6
- Rep Power
Vikpaw is largely right about bulk upload. At present RECORDLINKwriter can upload one document to multiple students but the question we are often asked is how to save multiple documents to multiple student records, eg a mail merge with variable data fields, in one action. To my knowledge there is no way of doing this. When its a matter of hard copy the solution is to scan the multiple documents as one large file and then use eCopy PaperWorks to split the document into individual files and send them to the SIMS DMS, but its not a task that I would enjoy. If its PDF, its still a manual job to upload the files from eCopy PaperWorks but at least the scanning element is missed.
Tim (Cohesion Software)
10th March 2012, 10:49 AM #7
I just went to Individual Report -> Word Template -> Developer -> Visual Basic
Originally Posted by vikpaw
R Click This Document (I think) -> New Code
I left the password protected macros alone - they're in the .dot file. The macros I created were in the .xml file.
Private Sub Document_Open()
Let me know if you want more detail and I'll go back and take a look.
I'd be interested in this. I can't find it on SupportNet - but SupportNet Search is a PITA.
Originally Posted by vikpaw
Thanks. Since the file name contains the student's name and Unique ID, it would hardly be a huge programmatic challenge for them to have a bulk upload option!
There is no easy way to bulk upload documents to the DMS.
10th March 2012, 02:21 PM #8
Ok, i'm struggling through this. I somehow made an AutoOpen macro that worked on open. This was stored in the .xml file.
What we need is to use the AutoExec routine, which then runs when Word is initiated, the problem with this is that it is an exception to the rule, so just having it in the .xml isn't good enough.
It will not run automatically unless it is stored in one of the following: the Normal template, a template that is loaded globally through the Templates and Add-Ins dialog box, or a global template stored in the folder specified as the Startup folder.
I don't think putting it in the normal template is a good idea, unless you put it in while you process the reports then take it out again. So we have to load it in a global template somehow. I looked at the Templates and Add-Ins dialog and nothing is loaded but i can choose something, so it looks like the setting will be machine / user centric. Which could be okay. I don't get how if you load a template in that dialog, it then loads the macro, how will it know it's linked to the document in question and run. Or will it just run because the rules are applied. Not sure, need to experiment some more.
What version of Office / Windows are you on @eean ? It probably affects things too.
Last edited by vikpaw; 11th March 2012 at 08:07 AM.
Reason: poor grammar!
10th March 2012, 02:31 PM #9
I put it into my normal.dotm and told it to load in that dialog and it says it's already loaded, and show it, which it didn't before.
Now on open, it tries to run the macro, but doesn't appear to have already run it which isn't what we want.
Also, it's trying to run it, before the document is loaded so it's erroring and saying this is no open document.
To top it off, as it's in normal.dotm it affects all docs i open, and gives me the same error.
Need some VBA ninja to assist.
11th March 2012, 06:39 AM #10
I've had it working on Windows XP, 7, Office 2003, 2007 and 2010!
Originally Posted by vikpaw
11th March 2012, 08:12 AM #11
I have got it working with AutoExec with a delay so it doesn't error on not having a document. However it only runs after the fact. If I export / upload, it hasn't run the macro yet. Whether it's held in normal or the .xml template. It runs on open only, if i suppress macros during open, the changes are not there, so whatever process does the uploading isn't triggering the macro. I'm guessing it really needs to be in that template that is locked out by password.
I found a Microsoft KB (286310) that states AutoExec wont run if Word is started by code, e.g.
Set oWord = CreateObject("Word.Application")
oWord.Visible = True
which i suspect is how it works.
I'm now wondering if the guy on SupportNet that did manage it, only had it working on open / access and all users have macro settings low enough. Or they have not realised it's an issue.
By theeldergeek in forum Mac
Last Post: 5th March 2010, 05:19 PM
By park_bench in forum Coding
Last Post: 30th June 2008, 09:52 AM
By contink in forum Scripts
Last Post: 25th December 2006, 10:15 PM
By tickmike in forum Windows
Last Post: 9th July 2006, 04:42 PM
By beeswax in forum Windows
Last Post: 8th October 2005, 02:59 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)