Follow @dporton

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

2×4 Survey Tester

Recently we had the opportunity to take a look at this great tool by 2×4. Survey Tester initiates process orientated automatic testing scenarios in which tester can flag problems directly on the screen. SurveyTester automatically creates screenshots and stores them so they can be reviewed by survey authors. Every test run is stored in the database and testers and survey authors can see in detail all the steps needed to replicate an identified issue. In addition, test runs can be exported and viewed individually or together through the visual work flow diagram.

Some of the features are

  • Cloud solution no need to install software.
  • One platform for testers, teams, survey authors and project managers.
  • SurveyTester now automatically detects which survey pages are mobile friendly.
  • Cost effective & time saving workflow process.
  • Creates a clear communication channel for test runs.

2×4 have made some recordings that you can take a look at the product. I recommend you do so, it could save you and your team allot of trouble.

https://www.youtube.com/watch?v=1UV9smK3AcA&feature=youtu.be
https://www.youtube.com/watch?v=4xJKMaKtGCU&spfreload=10

Testing Unicom Intelligence with Office 2016

Just the other day I was required to upgrade to office 2016. I have set this post up to record any issues I find and record what I have tested. If you have done any testing on 2016 then please feel free to leave a comment and we can add it to the list of things that may need to be looked at by the Unicom Team.

Product Test Outcome Date
Excel Can I use my existing VBA code to manipulate XL files Yep , no issues here everything currently still works 5/06/2017
Excel Can I export an MTD to an XL file Yep , no issues here everything currently still works 21/06/2017
Excel Can I export data file to a csv file that can be loaded into XL Yep , no issues here everything currently still works 21/06/2017
Powerpoint Can I use my existing VBA code to manipulate ppt/pptx files Yep , no issues here everything currently still works 5/06/2017
Powerpoint Can export my tables to a PPT/PPTX still using my old chart templates Yep , no issues here everything currently still works 5/06/2017
Powerpoint create a batch file and run an MRS that holds a TOM export to PPT Excel seams to hang at end of export ( I think when it is saving the PPT ) Added code to kill process “TASKKILL /F /IM excelcnv.exe” to see if I can kill it 21/06/2017

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

							

Me and Confirmit Exports

I have been working for a few weeks now on data exported from Confirmit and i thought i would share some of the things i have to do and how i do them to get the sav file in to a workable format. These tips may just make your life a little easier if you are doing the same thing.

I would be interested in hearing the things you have to do when getting data from Confirmit. It seams a little strange to me that we have to do these things and it may just be our Vendor that needs teaching but if it is the Confirmit export itself then we may be able to help them make it better. Anyway please share your thoughts i am very interested in what others have to say.

%d bloggers like this: