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.

References :

http://support.microsoft.com/kb/245801

Excel VBA Language Reference

Find the code below...

Function createXLMacro(filePath)
 xlRange = "C13"
 'This is the cell position at which the file gets attached and embedded to the excel sheet
 
 Set objExcel = CreateObject("Excel.Application")
 Set objWorkbook = objExcel.Workbooks.Open("C:\Testfile.xls")
 'This is a saved template that opens at runtime
 
 objExcel.Visible = False
 'Hide Excel
 
 filePath = Split(filePath,"|")
 'If you are attaching multiple file attachments in one go, then send the multiple file paths separated by "|"
 
 numberOfAttachments = Ubound(filePath)
 
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set MyFile = fso.CreateTextFile("c:\xlMacro.txt", True)
 MyFile.WriteLine("Attribute VB_Name =" & Chr(34) & "xlMacro" & Chr(34))
 MyFile.WriteLine("Sub insertImage()")
 
 For i = 0 to numberOfAttachments-1
  
  MyFile.WriteLine("Range(" & Chr(34) & xlRange & Chr(34) & ").Select")
  MyFile.WriteLine("ActiveSheet.OLEObjects.Add(Filename:=" & Chr(34) & filePath(i) & Chr(34) & ", Link:=False, _")
  MyFile.WriteLine("DisplayAsIcon:=False, IconFileName:=" & Chr(34) & "" & Chr(34) & ", IconIndex:=0, _")
  MyFile.WriteLine("IconLabel:=" & Chr(34) & "1" & Chr(34) & ").Select")
  MyFile.WriteLine("Selection.ShapeRange.LockAspectRatio = msoFalse")
  MyFile.WriteLine("Selection.ShapeRange.Height = 13.00")
  MyFile.WriteLine("Selection.ShapeRange.Width = 45.75")
  MyFile.WriteLine("Selection.ShapeRange.Fill.Visible = msoTrue")
  MyFile.WriteLine("Selection.ShapeRange.Fill.Solid")
  MyFile.WriteLine("Selection.ShapeRange.Fill.ForeColor.SchemeColor = 48")
 
  xlRange = Left(xlRange,1) & CStr(CInt(Right(xlRange,2))+5)
  'Change '5' as this decides the number of rows to skip after every attachment
 Next
 MyFile.WriteLine("End Sub")
 MyFile.Close
 objExcel.VBE.ActiveVBProject.VBComponents.Import "c:\xlMacro.txt"
 'Inserts the macro at runtime usind the Visual Basic Editor API instance of Excel

 objExcel.Run "insertImage"
 'Run the specific function in the macro

 createXLMacro = xlRange
 'Returns the last used Excel Range

 objWorkbook.Save
 objWorkbook.Close
 Set objExcel = Nothing
 Set objWorkbook = Nothing
End Function