preload

Automatic Data Maintenance – Date/Time (Excel)

Posted by Albert Gareev on Jan 08, 2012
0
A few simple Excel macros I use for automatic data maintenance. Date Macro Current date/time =NOW() 9/7/2011 17:57 Date formatted as text =TEXT(NOW(),”mm/dd/yyyy”) 09/07/2011 Future date (5 days from today) =TEXT(NOW()+5,”mm/dd/yyyy”) 09/12/2011 A year 180 days before today =TEXT(YEAR(NOW()-180), “00”) 2011 A month 58 days from today =TEXT(MONTH(NOW()+58), “00”) 11 Year, formatted ...

How to use Dictionary object for storing Excel data ...

Posted by Albert Gareev on Jun 10, 2010
1
Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM – Open workbook – Open worksheet – map cell values to column names. Notes. Heading and trailing space characters are removed To make mapping not case sensitive index is brought to lower case If your spreadsheet allows column name duplication you need to enhance […] ...

How to use test data from a locked Excel Workbook ...

Posted by Albert Gareev on Apr 08, 2010
1
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 […] ...

How to check if Excel Workbook is locked

Posted by Albert Gareev on Apr 07, 2010
0
Parent page: Service Functions – Excel (QTP, VBScript) Answers Description Connect to Excel through COM – Open workbook – Check property. ...

How to find-replace empty cells (Excel, QTP, VBScript)

Posted by Albert Gareev on Jan 29, 2010
0
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 […] ...

How to find Excel worksheet by name (QTP, VBScript)

Posted by Albert Gareev on Jan 27, 2010
1
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 2. If you don’t know Excel Worksheet name exactly or not sure if it exists ...

How to add (create) Excel macro from QTP

Posted by Albert Gareev on Jan 21, 2010
0
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 ...

How to enlist Excel VBA macros (QTP, VBScript)

Posted by Albert Gareev on Jan 20, 2010
1
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 ...

How to exchange parameters with Excel VBA function ...

Posted by Albert Gareev on Jan 15, 2010
0
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 Excel/VBA function source code 2. To retrieve parameters from an Excel VBA function You need to reserve cells on a worksheet for that purpose. […] ...

How to call Excel VBA function from QTP

Posted by Albert Gareev on Jan 14, 2010
0
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. ...

Service Functions – Excel (QTP, TestComplete, ...

Posted by Albert Gareev on Jan 12, 2010
0
In the code examples I present I often refer to routine functions. In my framework I have specialized libraries to call from.  In my blog I maintain the similar structure. Functions Excel File Worksheet compare (QTP, VBScript) How to check if Excel Workbook is locked A little automation for VBScript OOP (1) A little automation for […] ...

How to open Excel workbook (QTP, VBScript)

Posted by Albert Gareev on Oct 17, 2009
0
Parent page: Service Functions – Excel (QTP, VBScript) Description Check the file exists – Create an instance of Excel by using COM – open (load) file ...

Excel File Worksheet compare (QTP, VBScript)

Posted by Albert Gareev on May 25, 2009
0
Original date: 19 Jan 2009, 2:00pm Parent page: Service Functions – Excel (QTP, VBScript) Excel application must be installed on the PC. Comparison goes cell by cell within the occupied range. Marks mismatching cells red. Both workbooks must exist and be defined as the full path with file. Both worksheets must exist and may be […] ...

Mapping Excel col names (QTP, VBScript)

Posted by Albert Gareev on May 23, 2009
1
Original date: 5 Dec 2008, 1:23pm Parent page: Service Functions – Excel (QTP, VBScript) ...

A little automation for VBScript OOP (2)

Posted by Albert Gareev on Nov 27, 2008
0
Generate code for Property Let / Property Get / Property Set methods of Delegator Object What to use 1. MS Excel worksheet as input / output interface 2. MS Excel VBA macro to generate source code 3. Input parameters Property Name – External name (used as Property Let / Get name) Parent Class – Name of […] ...

A little automation for VBScript OOP (1)

Posted by Albert Gareev on Nov 25, 2008
0
Generate code for Property Let / Property Get / Property Set methods What to use 1. MS Excel worksheet as input / output interface 2. MS Excel VBA macro to generate source code 3. Input parameters Property Name – External name (used as Property Let / Get name) Parent Class – Skip. Used in only with […] ...
Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported
This work by Albert Gareev is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported.