+ Post New Thread
Results 1 to 11 of 11
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 ...
  1. #1
    eean's Avatar
    Join Date
    May 2006
    Location
    Kuala Lumpur
    Posts
    559
    Thank Post
    65
    Thanked 52 Times in 37 Posts
    Rep Power
    29

    Getting a Word VB Macro to execute when sending Individual Report to document server

    Hi
    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?

    or

    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?

    Thanks,

  2. #2
    skunk's Avatar
    Join Date
    Mar 2006
    Location
    North West
    Posts
    311
    Thank Post
    88
    Thanked 40 Times in 33 Posts
    Rep Power
    29
    I stopped using Macros in word documents for this reason, but if someone knows of a way I would grateful.

  3. #3
    ajp233's Avatar
    Join Date
    Jun 2009
    Posts
    19
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  4. #4

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,375
    Thank Post
    368
    Thanked 635 Times in 517 Posts
    Rep Power
    157
    Could you sign your macros? Then have gpo to allow signed macros?

  5. #5

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by eean View Post
    Hi
    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?

    or

    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?

    Thanks,
    Hi @eean - I had a macro built into the sims template to fiddle with a font, but found it didn't work on generation.

    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.

  6. Thanks to vikpaw from:

    eean (10th March 2012)

  7. #6

    Join Date
    Jun 2008
    Posts
    5
    Thank Post
    7
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    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)

  8. Thanks to Tham from:

    vikpaw (6th March 2012)

  9. #7
    eean's Avatar
    Join Date
    May 2006
    Location
    Kuala Lumpur
    Posts
    559
    Thank Post
    65
    Thanked 52 Times in 37 Posts
    Rep Power
    29
    Quote Originally Posted by vikpaw View Post
    Did you add your own macro to the indrepbasetemplate.dot or just into the template you create through sims?
    I just went to Individual Report -> Word Template -> Developer -> Visual Basic
    R Click This Document (I think) -> New Code
    Type:
    Code:
    Private Sub Document_Open()
    
    End Sub
    I left the password protected macros alone - they're in the .dot file. The macros I created were in the .xml file.

    Let me know if you want more detail and I'll go back and take a look.

    Quote Originally Posted by vikpaw View Post
    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.
    I'd be interested in this. I can't find it on SupportNet - but SupportNet Search is a PITA.

    There is no easy way to bulk upload documents to the DMS.
    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!

  10. #8

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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 07:07 AM. Reason: poor grammar!

  11. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  12. #10
    eean's Avatar
    Join Date
    May 2006
    Location
    Kuala Lumpur
    Posts
    559
    Thank Post
    65
    Thanked 52 Times in 37 Posts
    Rep Power
    29
    Quote Originally Posted by vikpaw View Post
    What version of Office / Windows are you on @eean ? It probably has affects things too.
    I've had it working on Windows XP, 7, Office 2003, 2007 and 2010!

  13. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.
    Code:
    Set oWord = CreateObject("Word.Application")
    oWord.Visible = True
    
    oWord.Documents.Open "C:\YourFile.doc"

    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.

SHARE:
+ Post New Thread

Similar Threads

  1. Ricoh MFP C2800 - print to document server?
    By theeldergeek in forum Mac
    Replies: 19
    Last Post: 5th March 2010, 04:19 PM
  2. VB code to colour cells in a word table
    By park_bench in forum Coding
    Replies: 4
    Last Post: 30th June 2008, 08:52 AM
  3. Replies: 4
    Last Post: 25th December 2006, 09:15 PM
  4. Replies: 5
    Last Post: 9th July 2006, 03:42 PM
  5. text in word all greek to me
    By beeswax in forum Windows
    Replies: 14
    Last Post: 8th October 2005, 01:59 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •