21 May 2010

Inserting a macro in Excel at runtime using VBscript in HP Quick Test Professional

Have you ever tried embedding a file in an excel file? When i say embedding i mean that it is not linking to any external file location in your hard disk. The file gets attached to the excel so that it is available every time you open it anywhere. Automating this in Quick Test Pro is a challenging task. This is many times required during reporting tasks in general Test Automation.

This is technically achieved in Excel by "Insert an Object". I have tried automating it in QTP, however, it was really difficult to write a plain code using excel VBA. The excel VBA conversion in vb-script is really difficult in this case. A unique approach which i felt is really useful is to inject a macro in to excel at runtime and triggering the macro to insert the required file (any file - image, doc, pdf, png, jpeg, etc.).

The approach is to write a macro in to text file dynamically at runtime and importing it in to the excel using Visual Basic Editor instance and running it. This saves the conversion into vb-script from Excel VBA and lets the macro run in excel in its native VBA code.

Please find the code below which tries to do the same. This code is written to accept multiple attachment paths separated by a "|" and attach these files into the excel at a distance of 5 rows from each other.