HP ALM Workflow VBScript

This category will provide articles regarding usage of Visual Basic Script in the workflow module of HP ALM.

In order to reference a defect / bug currently selected by a user in the Defect GridView the following method can be used.

Below method gets any row from a provided Microsoft Excel worksheet as a String array. This can be quite handy, if you for example want to get all field names from a worksheet.

Source Code

The code references a Microsoft Excel API. It automates Excel in a background process and therefore requires Excel to be running as well as the parent workbook of a handed worksheet to be opened before calling this function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
' @Author - Alexander Bolte
' @ChangeDate - 2014-12-11
' @Description - Returns the header row of a provided Excel.Worksheet object
' as a zero based String() Array.
' @Param worksheet - an Excel.Worksheet object of an Excel.Workbook, 
' which has been opened before calling this function. 
' @Param row - a Long indicating the row, which field headers
' should be read from.
' @Returns a zero based String() Array containing the field header names.
' @Remarks - The method starts always in column one in a handed worksheet.
Function getExcelHeader(workSheet, row) ' As String()
  Dim c ' As Integer
  Dim header ' As String()
  Dim importRng ' As Range
 
  Set importRng = workSheet.UsedRange
  ReDim header(importRng.Columns.Count - 1)
  For c = 0 to UBound(header)
      header(c) = cStr(workSheet.Cells(row,c + 1).Value)
  Next ' column in worksheet.
  Set importRng = Nothing
 
  getExcelHeader = header
End Function

If you need to get the parent directory of a given path to a file or a directory, the following function might help.

Not the best performance I have to admit since it is relying on String operations.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2015-02-10
' @Description - Returns the path to a parent directory for a handed path.
' @Param path - a String providing a path to a directory or a file.
' @Returns a String providing a path to a parent directory including the
' last path delimiter. If an error occurs, an empty String is returned.
Function getParentDirectoryPath(path) ' As String
  Dim dirPath ' As String
  Dim splitted ' As String
  Dim i ' As Integer

  On Error Resume Next

  dirPath = ""
  splitted = split(path, "\")
  For i = lBound(splitted) To uBound(splitted) - 1
     dirPath = dirPath & splitted(i) & "\"
  Next

  If Err.Number <> 0 Then
     dirPath = ""
     Err.Clear
  End If

  getParentDirectoryPath = dirPath
End Function