+ Post New Thread
Results 1 to 6 of 6
MIS Systems Thread, CMIS to EXCEL VBA functions in Technical; Attached is a zip containing a spreadsheet that through the magic of VBA Macro's allows Excel to query some basic ...
  1. #1


    Join Date
    May 2009
    Posts
    3,391
    Thank Post
    301
    Thanked 916 Times in 684 Posts
    Rep Power
    346

    CMIS to EXCEL VBA functions

    Attached is a zip containing a spreadsheet that through the magic of VBA Macro's allows Excel to query some basic student information from a CMIS database. Three examples are given, one to return student id(s) given some search criteria, one to return teaching groups given a student id and one to return any field from the students table given a student id. Results from queries are held in a simple cache to aid performance (but don't expect to use this to produce large 'live' worksheets that fly along).

    As given the workbook will need to be told where your database resides and the name of it. You will also need appropriate access permissions associated with your AD/Windows credentials. I recommend not accessing the CMIS database directly but create a separate DB and create views to allow read only access to the relevant tables or access a test/non live instance.

    To set up, open the workbook but do not enable active content. Fill in the Server and Database cells (highlighted in red on the config tab) and then save and close the workbook. Now open the workbook, enable Macros and switch to the functions tab to see some examples of the function calls. If you didn't enter the database information correctly, the sheet will appear to hang as it tries to establish a connection to the DB and waits and waits and waits for the connection to timeout. If you did enter it correctly, you should see that the functions have returned some results.

    For other use, the server and database names can be hard coded into the DBDefaults procedure (in the VBA). This avoids using cells in the worksheet to hold the values and the VBA can then be locked before it is accessed by end users.

    Those with modest VBA and SQL skills and some knowledge of CMIS back end will easily be able to extend this by providing functions to access other areas of CMIS. If you do write other functions, please share them here.

    This code is provided as is without warranty - use at your own risk etc. If it parties in your house till 3am, wakes the neighbours is sick on the carpet and eats your cat, do let me know but only because I like a laugh. Copyright is implicit - not having a copyright messages don't mean you can do what you like without permission. IT and MIS support Staff working in schools may make use of this code for use in their schools. If you go selling it for profit or start claiming it was you wot wrote it, I'll come find you and give you a jolly good telling off which may not be limited to words like "naughty", "bad" and "dishonourable cad". In summary - non profit use is just dandy but please don't profit from it without at least asking.

    Attachment 11672

  2. #2
    mark80's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    218
    Thank Post
    27
    Thanked 12 Times in 10 Posts
    Rep Power
    18
    ^^Link no worky

  3. #3


    Join Date
    May 2009
    Posts
    3,391
    Thank Post
    301
    Thanked 916 Times in 684 Posts
    Rep Power
    346
    How very odd. I tested it after I posted and it was fine. Now the file has disappeared from my attachments list. Here it is again.
    Attached Files Attached Files

  4. Thanks to pcstru from:

    mark80 (19th October 2011)

  5. #4


    Join Date
    May 2009
    Posts
    3,391
    Thank Post
    301
    Thanked 916 Times in 684 Posts
    Rep Power
    346
    Mark80 (and anyone else who is giving this a go). Can you please let me know how you get on with it? Does it work, did you find it easy to setup? Would you find this kind of access useful?

  6. #5


    Join Date
    May 2009
    Posts
    3,391
    Thank Post
    301
    Thanked 916 Times in 684 Posts
    Rep Power
    346
    Another function for the VBA. This one simply allows you to run any arbitrary SELECT against the database, passed as a string. As with other functions, the iPos parameter can be used to access individual rows where the SQL returns many rows.

    Code:
    '---------------------------------------------------------------------------------------------------
    ' Function   : DBGenSQL
    '
    ' Notes      : Run any supplied SQL against the database with cached results
    '
    ' Parameters : SQL   - Query String (Standard MS SQL query BUT with only one column!)
    '              iPos  - Result Set Row number to access specific row where a query returns
    '                      a multirow result set.
    '---------------------------------------------------------------------------------------------------
    Function DBGenSQL(SQL, Optional iPoss As Variant) As Variant
       
       On Error GoTo DBGenSQLErr
       
       Dim sID As String, _
           sSQL As String
              
       If IsMissing(SQL) Then
          sID = "Error : Missing Query "
          Err.Raise (20003)
       Else
          sSQL = SQL
       End If
       
       If IsMissing(iPoss) Then
          iPoss = -1
       End If
    
       ' Check if we are looking for one in a set
       If iPoss > -1 Then
          Dim i As Integer
          i = iPoss.Value - 1
          sID = SCSQLRes(sSQL, i)
       Else
          sID = SCSQLRes(sSQL, -1)
       End If
    
    DBGenSQLErr:
       On Error GoTo 0
       DBGenSQL = sID
    
    End Function
    '---------------------------------------------------------------------------------------------------

  7. #6


    Join Date
    May 2009
    Posts
    3,391
    Thank Post
    301
    Thanked 916 Times in 684 Posts
    Rep Power
    346
    Err ... hope this isn't seen as spamming, what with me talking mostly to myself here but I figure these will be useful to someone, somewhere, someday.

    The attached zip contains an update which includes the generic SQL (previous post) and an all new DBStudAssess - which can be used to retrieve assessment data from CMIS. Follow carefully the instructions in the first post or watch your PC crash and burn as hundreds of individual queries wait their turn to time out.

    The Assessment Example tab shows how to use the StudAssess function to pull out targets and assessments for three subjects (English/Maths and Science) over the full KS3 to KS4 period (so for year 11 pupils you can see their progression over 5 years starting in year 7). Your CMIS Template names and subject codes may well be different - if so just type the appropriate ones into the green boxes and the function should pick up the relevant data. The function should be able to handle any criteria you have in your templates.

    The example also shows how to use the DBGenSQL function so that after selecting a year on the assessment tab, the tutor groups in the dropdown are updated for the selected year (the named range for tutorgroups is on the Lookups tab). This is a really nice and quite easy way to filter data in drop downs pick lists dynamically based on another selection. Can't think why I haven't done this before!
    Attached Files Attached Files



SHARE:
+ Post New Thread

Similar Threads

  1. [ACS] Migrating CMIS to a new server
    By brickwall53 in forum MIS Systems
    Replies: 13
    Last Post: 6th October 2011, 11:10 PM
  2. CMIS to SIMS
    By SpuffMonkey in forum MIS Systems
    Replies: 10
    Last Post: 11th November 2008, 04:21 PM
  3. Writing to Excel from .vbs script
    By Samson in forum Windows
    Replies: 6
    Last Post: 15th October 2008, 08:34 AM
  4. Excel VBA Loop Question
    By vunsev in forum Coding
    Replies: 5
    Last Post: 11th April 2008, 09:08 AM
  5. Macro and VBA functions
    By randle in forum Windows
    Replies: 10
    Last Post: 9th February 2007, 10:32 AM

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
  •