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 Author!
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 = ...
