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.

References :

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.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
 MyFile.WriteLine("End Sub")
 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

 Set objExcel = Nothing
 Set objWorkbook = Nothing
End Function

1 comment:

  1. This is not working.. It gives us error when we import xlMacro.txt line 40. and line 43