Follow @dporton

JustCode : Find first cell with specific content

This function will find the first cell of the active worksheet with a specific content and return the column or row number.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Function to find first cell with specific content
' ****************************************

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 = FindFirstCell(3,9,"C","",oSheet)


Function FindFirstCell(iCol,iRow,sDirection,sLookFor,oSheet)

     Do while oSheet.Cells[iRow][iCol].text <> sLookFor
         Select Case sDirection
             Case = "C"
                 iCol = iCol + 1
             Case = "R"
                 iRow = iRow + 1
        End Select
     Loop

     Select Case sDirection
         Case = "C"
             FindFirstCell = iCol - 1
         Case = "R"
             FindFirstCell = iRow - 1
     End Select

End Function

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

Debugging your surveys with Chrome.

Hello , first off sorry for all the coughs and splutters in this recording but i just had to share what i discovered today. I am sure some of you already new this , but for me it was a life saver. Made my editing allot less painful. I hope you find it useful also.

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()
%d bloggers like this: