Follow @dporton

JustCode : Loop columns in an excel sheet

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

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

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) )

JustCode : Store different timzone time in a variable

This function finds the time in a different timezone and stores it as a UTC time then converts it back to the local time

 ' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 18th July 2009
' Store different timzone time in a variable.
' ****************************************

Dim oDateTimeNow

    ' Get Perth Time
    oDateTimeNow = now(225)

    debug.Log ( "Time in Perth :" + ctext(oDateTimeNow) )

    ' Convert Perth time to localtime
    oDateTimeNow = LocalToUTCTime(oDateTimeNow)
    debug.Log ( "UTC Time :" + ctext(oDateTimeNow) )

    oDateTimeNow = UTCToLocalTime(oDateTimeNow)

    debug.Log ( "Time in Localtime :" + ctext(oDateTimeNow) )

JustCode : Create a directory if it does not exist

This code shows us how to check for a directory and if it was not found it will be created.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 23rd July 2009
' Create a directory if it does not exist
' ****************************************

Dim oFSO

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    If oFSO.FolderExists("C:\Output Files") = False Then
        oFSO.CreateFolder("C:\Output Files")
    End if

Set oFSO = Null

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()

%d bloggers like this: