Follow @dporton

JustCode : Delete a file if it exists

This code shows us how to check for a file and delete it if it already exists.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 23rd July 2009
' Delete a file if it exists
' ****************************************

Dim oFSO
    If oFSO.FileExists("C:\TEMP\test.txt") = True Then
        oFSO.DeleteFile("C:\TEMP\test.txt")
    End if
Set oFSO = Null

JustCode : Create a new file with a line of text in it

This code shows us how to create a new file and add a line of text to it.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 23rd July 2009
' Create a new file with a line of text in it
' ****************************************

Dim oFSO, oFile
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = oFSO.CreateTextFile("C:\TEMP\test.txt", True)
    oFile.WriteLine("HELLO WORLD")
    oFile.Close()
    Set oFSO = Null

JustCode : Add a language to an MDD

This “Just Code” post will show you how to add a language to an MDD file via script.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 26th September 2009
' Open MDD file and add language
' ****************************************

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:\temp\New.mdd", ,2)
    oMDM.Languages.Add("JPN")
    oMDM.Save()
    oMDM.Close()

JustCode : Add / unlock a version in your MDD

When you are playing arround with MDD files you may need to add or unlock the version. This simple code shows you how to do it.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th July 2009
' Sub to unlock a MDD version.
' ****************************************

Dim oMDM,oVar,oElement,sLabel
Set oMDM = CreateObject("MDM.Document")
oMDM.Open("c:\temp\short_drinks.mdd", , openConstants.oREADWRITE)
oMDM.Versions.AddNew()
oMDM.Save()

JustCode : Updated Functions for GetMrData.xls

This update getdata function will split the data across multiple tabs.

Sub GetData()

'variables used to invoke the standard MS Data Link Properties dialog
Dim DataLinkHelper

'variables used to access ADO
Dim ADO
Dim SQLQuery
Dim RecordSet
Dim Field

'variables used to populate Excel cells
Dim XLSheet
Dim XLStartRow
Dim XLStartColumn
Dim XLCol
Dim XLRow
Dim iFieldsPerSheet
Dim oSheet

'have the user specify the data source they wish to use
Set DataLinkHelper = CreateObject("MROLEDB.DataLinkHelper")
Result = DataLinkHelper.DisplayWizard() 'this will return the connection string
If Result = "" Then 'User Canceled
      Call MsgBox("Data source selection canceled by user.")
      Exit Sub
Else 'instantiate an ADO object and give it the connection string returned by the Data Link Properties dialog
     Set ADO = CreateObject("ADODB.Connection")
     ADO.ConnectionString = Result
     ADO.Open
End If

'execute a query that will return all the data
'from the data source specfied by the user
SQLQuery = "select * from vdata"
Set RecordSet = ADO.Execute(SQLQuery)

'now iterate thru the returned recordset to populate the Excel worksheet
XLSheet = 1
XLStartRow = 5
XLStartColumn = 1
For Each oSheet In Worksheets
    Worksheets(XLSheet).Cells.ClearContents
    XLSheet = XLSheet + 1
Next

XLCol = XLStartColumn
XLRow = XLStartRow
iFieldsPerSheet = 10
XLSheet = 1

For Each Field In RecordSet.Fields
    Worksheets(XLSheet).Cells(XLRow, XLCol).FormulaR1C1 = Field.Name
    XLCol = XLCol + 1
    If (iFieldsPerSheet < XLCol) Then
        XLCol = XLStartColumn
        XLSheet = XLSheet + 1
        If (Sheets.Count < XLSheet) Then
            Sheets.Add After:=Sheets(Sheets.Count)
        End If
    End If
Next

If Not RecordSet.EOF Then
    XLRow = XLStartRow + 1
    Do Until RecordSet.EOF
        XLCol = XLStartColumn
        XLSheet = 1
        For Each Field In RecordSet.Fields
            Worksheets(XLSheet).Cells(XLRow, XLCol).FormulaR1C1 = RecordSet(Field.Name)
            XLCol = XLCol + 1
            If (iFieldsPerSheet < XLCol) Then
                XLCol = XLStartColumn
                XLSheet = XLSheet + 1
                If (Sheets.Count < XLSheet) Then
                    Sheets.Add After:=Sheets(Sheets.Count)
                End If
           End If
       Next
      XLRow = XLRow + 1
      RecordSet.MoveNext
      DoEvents
    Loop
End If
ADO.Close

End Sub

JustCode : Loop File a line at a time

This code shows us how to open a file in readonly mode and skip through it a line at a time.

’ ****************************************
‘ Designed by : Smarter Dimensions
‘ Last Updated : 24th July 2009
‘ Loop File a line at a time.
‘ ****************************************

Dim oFSO, oFile

Set oFSO = CreateObject(“Scripting.FileSystemObject”)
Set oFile = oFSO.OpenTextFile(“C:\temp\test.txt”,1,false,0)

Do While oFile.AtEndOfStream = false
DEBUG.Log(oFile.ReadLine)
Loop

oFile.Close()

							

JustCode : Copy one context to another

This JustCode scripts shows you how to loop the questions in your MDD and copy one context label to another. This can be useful when you are setting up mdd files with multiple contexts.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th July 2009
' Sub to copy the labels from one context to another.
' ****************************************
CopyContextLabels("c:\temp\short_drinks.mdd","Question" , "CATI")

Sub CopyContextLabels(sFile,sFrom,sTo)

    Dim oMDM,oVar,oElement,sLabel

    Set oMDM = CreateObject("MDM.Document")

    oMDM.Open(sFile, , openConstants.oREADWRITE)

    oMDM.Versions.AddNew()

    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oMDM.Contexts.Current = sFrom
            sLabel = oVar.Label
            oMDM.Contexts.Current = sTo
            oVar.Label = sLabel
            oMDM.Contexts.Current = sFrom

            For each oElement in oVar.Elements.Elements
              oMDM.Contexts.Current = sFrom
              sLabel = oElement.Label
              oMDM.Contexts.Current = sTo
              oElement.Label = sLabel
           Next
        end if
    Next

    oMDM.Save()

End Sub
 

JustCode : Sub to change the label on a specified question.

This JustCode script shows us how to find a question and changes its label to a new string.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Sub to change the label on a specified question.
' ****************************************

ChangeQuestionText("c:\temp\short_drinks.mdd","Question","gender","NewText")

Sub ChangeQuestionText(sFile,sContext,sQuestion,sText)

Dim oMDM

Set oMDM = CreateObject("MDM.Document")

oMDM.Open(sFile, , 2)

oMDM.Contexts.Current = sContext

oMDM.Variables[sQuestion].Label = sText

oMDM.Save(sFile)

Set oMDM = null
End Sub

JustCode : Open MDD file

This JustyCode script shows you how to open up an MDD file in read-only mode.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 22nd July 2009
' Open MDD file
' ****************************************

Dim oMDM

Set oMDM = CreateObject("MDM.Document")
oMDM.Open("c:\temp\short_drinks.mdd", , 1)

' Do something ...

oMDM.Close()

JustCode : Loop rows in an excel sheet

This JustCode script shows us how to open up an excel workbook and select the active sheet and loop the first ten rows displaying the values of the first cell.

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

Dim oExcel,oWorkBook,oSheet
dim iRow,oCell,iStartRow,iEndRow

Set oExcel = createobject("Excel.Application")

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

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

Set oSheet = oWorkBook.ActiveSheet

iStartRow = 1
iEndRow = 10

For iRow = iStartRow to iEndRow
    Set oCell = oSheet.Cells[iRow][1]
    Debug.Log(oCell.value)
Next
%d bloggers like this: