Just Code

JustCode : Update a sample record

by

This code shows you how to update a sample record from an MRS script.

Dim oConnection

' Start code to check if the field has data in it.
    Set oConnection = CreateObject("ADODB.Connection")

' Standard Connection String You need to change this
    oConnection.ConnectionString = "Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=-1;User ID=MyUser;Initial Catalog=Participants;Data Source=MyServer"
    oConnection.Open()

    If ( oConnection.State = 1 ) Then
        oConnection.Execute("UPDATE TABLE1 SET Queue = 'Fresh' WHERE Queue = 'Completed'")
    End If

    oConnection.Close()

Set oConnection = Null

JustCode : Strip out html from metadata context

by

This code will show you how to strip out html or unwanted characters from a specified context in your mdd file Via a DMS script.

Event(OnBeforeJobStart, "")

    Dim oMDM
    ' Create the MDM object and open the Short Drinks .mdd file in read-write mode
    Set oMDM = CreateObject("MDM.Document")
        oMDM.Open("C:\survey.mdd", ,2)
        oMDM.Contexts.Current = "Question"

    Dim oVar,oElement
    'Using the StripHTML funciton
    
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripHTML ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripHTML ( oElement.Label )
            Next
        end if
    Next

    'Using the StripOut function
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripOut ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripOut ( oElement.Label )
            Next
        end if
    Next

    oMDM.Save("C:\survey.mdd")
    Set oMDM = null

    Function StripHTML ( sText )
        Dim objRegExp, strOutput
        Set objRegExp = CreateObject("VBScript.RegExp")
            objRegExp.IgnoreCase = True
            objRegExp.Global = True
            objRegExp.Pattern = "< (.|n)+?>"

            'Replace all HTML tag matches with the empty string
            strOutput = objRegExp.Replace(sText, "")

            'Replace all < and > with < and >
            strOutput = Replace(strOutput, "< ", "<")
            strOutput = Replace(strOutput, ">", ">")

            StripHTML = strOutput    'Return the value of strOutput

        Set objRegExp = null
    End Function

    Function StripOut ( sText )
    ' You can use this function to hard code specific things you want to clean out the text
    Dim sAnswer
        sAnswer = sText
        ' Just repeat this line with the items you want to remove
        sAnswer = Replace(sAnswer,"[WHAT_ARE_YOU_LOOKING FOR]","")
        StripOut = sAnswer

    End Function

End Event

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location="C:\survey.ddf;Initial Catalog="C:\survey.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location="C:\survey_out.sav"
    MetaDataOutputName = "C:\survey_OUT.mdd"
End OutputDatasource

JustCode : Strip out new lines from text questions

by

This code shows us how to strip out new lines from the text questions in our survey via a DMS script.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 30th July 2009
' Delete new lines from text questions
' ****************************************

Event(OnNextCase, "Next")
Dim iQuestionCount
    For iQuestionCount = 0 To dmgrJob.Questions.Count - 1
        If dmgrJob.Questions[iQuestionCount].response.DataType = 2 then
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.Cr," ")
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.lf," ")
        End If
    Next
End Event

JustCode : Delete Empty columns of data

by

This mrs script shows you how you can use mrs files to make and run a dms file that will strip out any columns that do not have any data in.

Step 1: Open the VQ file and make an MDD FILE.

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\FILE"
    Set mrMdsc = createobject("mrdscreg.components")
    Set mrMDSC = mrMdsc["mrScDsc"]

    Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")
        oDoc.Save(sVQ + ".mdd")
        oDoc.Close()

Set oDoc = Null

Step 2: Take the MDD file and loop the variable instances


Dim oMDM, oVar
Dim sSQL,oConnection,oRecordset

' Create the MDM object
Set oMDM = CreateObject("MDM.Document")
    oMDM.Open("C:\temp\FILE.mdd", , 1)

    For Each oVar in oMDM.Variables
        'if oVar.IsSystem = False then
            debug.Log("Question Name : " + oVar.FullName)

            ' Start code to check if the field has data in it.
            Set oConnection = CreateObject("ADODB.Connection")
                oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.VQ;Initial Catalog=C:\temp\FILE.mdd"
                oConnection.Open()

            If ( oConnection.State = 1 ) Then
                Set oRecordset = CreateObject("ADODB.Recordset")
                    oRecordset.Open("SELECT count(respondent.serial) FROM VDATA WHERE " + oVar.FullName + " is not null" ,oConnection,3,1)
                    If ( oRecordset.EOF = true and oRecordset.BOF = true ) Then
                        sSQL = sSQL
                    ELSE
                        sSQL = sSQL + oVar.FullName + ","
                    End if
                Set oRecordset = Null

            End If

            oConnection.Close()
            Set oConnection = Null
    ' end if
Next

oMDM.Close()
sSQL = Left(sSQL,len(sSQL)-1)
debug.Log(sSQL)

Step 3: Create the dms file with the select statement in it.


Dim oFSO, oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.CreateTextFile("C:\temp\FILE.dms", True)


oFile.WriteLine("InputDatasource(Input, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.vq;Initial Catalog=C:\temp\FILE.mdd"" ")
oFile.WriteLine("    SelectQuery = ""SELECT " + sSQL + " FROM VDATA""")
oFile.WriteLine("End InputDatasource")
oFile.WriteLine("")
oFile.WriteLine("OutputDatasource(Output, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location=C:\temp\Final.sav""")
oFile.WriteLine("    MetaDataOutputName = ""C:\temp\Final.mdd""")
oFile.WriteLine("End OutputDatasource")

oFile.Close()

Set oFSO = Null

Step 4: Run the file


Dim oDMOMJob

Set oDMOMJob = CreateObject("DMOM.Job")

oDMOMJob.Load("C:\temp\FILE.dms", null)

oDMOMJob.Run()

Just Code : Run MRS from an MRS

by

This shows us how we can run an MRS from within an MRS file.

Dim oScriptEngine, sScript, oProgram, sResult

' Define the test script
sScript = "Dim x, y, result" + mr.CrLf + _
"x = ""Hello """ + mr.CrLf + _
"y = ""World""" + mr.CrLf + _
"result = x + y"

' Create the script engine and parse
Set oScriptEngine = CreateObject("mrScript.ScriptEngine")

Set oProgram = oScriptEngine.Parsers[0].Parse(sScript, oScriptEngine, 0)

' Execute the script
oScriptEngine.Execute(oProgram, 0)

' Get the result variable value
sResult = oProgram.Variables["result"]

JustCode : Code to open up an exisiting excel workbook

by

This code shows us how to open up an existing excel file and select the active workbook.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Code to open up an exisiting excel workbook
' ****************************************

Dim oExcel,oWorkBook

Set oExcel = createobject("Excel.Application")

oExcel.Workbooks.Open("C:\TEMP\ExcelExport.xls")

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

JustCode : Loop columns in an excel sheet

by

This code will open up an existing excel workbook and select the active sheet then loop through the first ten cells of the first row showing the cell contents

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Code to loop columns in an excel sheet
' ****************************************

Dim oExcel,oWorkBook,oSheet
dim iCel,oCell,iStartCol,iEndCol

Set oExcel = createobject("Excel.Application")

oExcel.Workbooks.Open("C:\TEMP\ExcelExport.xls")

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

Set oSheet = oWorkBook.ActiveSheet

iStartCol = 1
iEndCol = 10

For iCel = iStartCol to iEndCol
     Set oCell = oSheet.Cells[1][iCel]
     Debug.Log(oCell.value)
Next

JustCode : Change status of a Project on specified date

by

This script shows you how to change the status of a project on a specific date via the use of the DPM objects.

Const SERVER = "ENTER_SERVERNAME_HERE"
Const USERNAME = "ENTER_USERNAME_HERE"
Const PASSWORD = "ENTER_PASSWORD_HERE"

Dim dpmAgent,dpmProject,dpmProjects
Dim oArray[10][4],iProjectCount
    oArray[0][1] = "J2001"
    oArray[0][2] = "25/12/2009"
    oArray[0][3] = "Inactive"
    oArray[1][1] = "J9001"
    oArray[1][2] = "25/12/2009"
    oArray[1][3] = "Inactive"

Debug.Log("Creating Agent object...")
Set dpmAgent = CreateObject("SPSSMR.DPM.Security.Login.Agent2")
    Debug.Log("Login as specified user...")
    dpmAgent.ConnectToDPMServer(SERVER)
    dpmAgent.Login(USERNAME,PASSWORD,Null)
    Set dpmProjects = dpmAgent.Server.Projects

iProjectCount = 0
    Do While (oArray[iProjectCount][1] <> "")
        On Error Goto ProjectNotFound
        If ( oArray[iProjectCount][2] = format(now(),"ddmmyyyy") ) Then
            Debug.Log("Get the project from DPM...")
            Set dpmProject = dpmAgent.Server.Projects[oArray[iProjectCount][1]]
            dpmProject.properties["Status"] = oArray[iProjectCount][3]
        End If
ProjectNotFound:

On error Goto 0
        iProjectCount=iProjectCount+1
Loop
Debug.Log("Logging out of Agent...")
dpmAgent.Logout()

JustCode : Store Current UTCDateTime in a variable

by

This code will get the current time and convert it to UTC time and then store it in a variable

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 18th July 2009
' Store Current UTCDateTime in a variable.
' ****************************************

Dim oDateTimeNow
    oDateTimeNow = now()
    debug.Log ( "FULL DATETIME :" + ctext(oDateTimeNow) )
    oDateTimeNow = LocalToUTCTime(oDateTimeNow)
    debug.Log ( "FULL UTC DATETIME :" + ctext(oDateTimeNow) )