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.
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
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
- Hits: 2239
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.
' @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
- Hits: 2016
In order to reference a defect / bug currently selected by a user in the Defect GridView the following method can be used.
- Hits: 2055
HP ALM REST API Article Count: 3
This category is meant to hold all articles regarding HP ALM REST API.
HP ALM Workflow VBScript Article Count: 16
This category will provide articles regarding usage of Visual Basic Script in the workflow module of HP ALM.
Page 6 of 7