An engineering approach to Software Testing and Test Automation

Parent page: Service Functions – Excel (QTP, VBScript) Question The question was asked here. How can I get data from Excel spreadsheet if it is accidentally open? It ruins whole batch execution. While opening manually I can go “read-only” and still access it. Is there any way to read data from an open and locked Excel file from QTP? Answer You can [...] ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Check property. Public Function ReadOnlyWorkbook(ByVal sFileName) Dim objXLBook, objXLHandle Dim FSO, boolRC Set FSO = CreateObject("Scripting.FileSystemObject") boolRC = FSO.FileExists(sFileName) Set FSO = Nothing If Not boolRC Then ReadOnlyWorkbook = FALSE Exit ...

Parent page: Service Functions – String (QTP, VBScript) Question The question was asked here. Hi Albert! Can you email me a QTP function that wraps line of text without breaking words? Thank you! Answer Proper text alignment is important. The whole layout of CSS block or HTML table could be broken if a text line won’t fit. The function below performs wrapping of [...] ...

Parent page: Service Functions – String (QTP, VBScript) Question The question was asked here. What QTP function i can use to trim inside string? For e.g. “text text text text” – i want only 1 space between. Answer I believe the sample string provided in the question originally contained multiple space characters inside but was already “trimmed” by HTML parser. [...] ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Open worksheet - Loop through data range – close and save the workbook. Note. In the example below script finds empty cells and assigns zero to them. Make sure you targeted the worksheet that you need. You can find it by index or by name. Set [...] ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Loop through worksheets – close and save the workbook. 1. If you know Excel Worksheet name and pretty sure it exists Set XLHandle = CreateObject("Excel.Application") XLHandle.DisplayAlerts = False Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls") On Error Resume Next Set ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Retrieve Code Module object - Add new macro string - close and save the workbook. Notes. For this example let’s assume our target Workbook is stored as “C:\Temp\1.xls”, and our target Code Module is named “Module1″. QTP/VBScript source code Set XLHandle = ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Retrieve Code Module object - Retrieve Code Block string - Parse the string - close and save the workbook. Notes. For this example let’s assume our target Workbook is stored as “C:\Temp\1.xls”, and our target Code Module is named “Module1″, and we’re looking for ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers 1. To pass-in parameters to an Excel VBA function A function must be public and belong to a Macro Module. QTP/VBScript source code Set XLHandle = CreateObject("Excel.Application") XLHandle.DisplayAlerts = False Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls") On Error Resume Next Call XLHandle.Run("ArgFunction", 5) boolRC ...

Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM - Open workbook - Invoke the function - Catch error - close and save the workbook. Notes. Macro (Sub or Function) must be public and belong to a Macro Module. Set XLHandle = CreateObject("Excel.Application") XLHandle.DisplayAlerts = False Set XLBook = ...