HP ALM

This category is a parent category in order to collect all sub categories and according articles.

The below method allows you to open an existing Excel file from a users hard drive.

Since it is using late binding you have to be a bit careful :0), however VBScript is not able to properly declare names anyway.

 Source Code

It is very important to regard that all references to Excel objects have to be released properly before quitting the Excel Application running as a background process.

This method automatically opens an instance of Excel and leaves it open after returning an opened workbook.

A function or sub calling this method therefore has to reference the parent Excel.Application object of a returned Workbook in order to handle an applications disposal.

After referencing the Excel.Application object you must not call its method quit until you are done processing all files opened by this instance.

Before calling the method Quit of an Excel.Application object you also have to ensure to close or save all Excel.Workbooks currently opened by an Excel.Application.

' @Author - Alexander Bolte
' @ChangeDate - 2014-06-17
' @Description - Returning an Excel.Workbook object
' referencing an Excel file available under provided file path.
' @Param filePath - A String pointing to an Excel file,
' which is processable by Excel version currently installed on a users machine.
' @Returns an Excel.Workbook object
' referencing a file available under given file path.
Function openExcelFile(filePath)
    Dim xlApp ' As Excel.Application
    Dim xlBook ' As Excel.Workbook

    On Error Resume Next

    ' Open a new instance of Excel on client machine.
    Set xlApp = CreateObject("Excel.Application")
    ' Try to open an Excel file.
    Set xlBook = xlApp.Workbooks.Open(filePath)

    If Err.Number <> 0 Then
       MsgBox Err.Description
       If Not (xlApp Is Nothing) Then
          ' Prevent Excel from asking, if changes to files should be saved.
          xlApp.DisplayAlerts = False
          ' Quit the application - all workbooks will be closed.
          xlApp.Quit
          Set xlBook = Nothing
       End If
       Err.Clear
    End If
    ' Clean up references, which are not needed anymore.
    Set xlApp = Nothing

    Set openExcelFile = xlBook
End Function

Example

The following example shows how to call above method and properly dispose of a running application and its resources. It is very important to dispose of Excel objects according to the Excel object tree. This means you should never dispose of a parent object before you dispose of its child objects. This would lead to runtime errors and possibly to unhandled, unwanted running instances of Excel in the background.

  ' Open Excel instance in background and reference application object.
  Set wrk = openExcelFile(filePath)
  Set app = wrk.Application

  '  ... do something with a file.

  ' Close referenced source workbook without saving changes before quitting the application.
  wrk.Close False
  set wrk = Nothing

  ' Quit the background Excel process.
  app.Quit
  Set app = Nothing

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

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

Subcategories

This category is meant to hold all articles regarding HP ALM REST API.

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