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.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
This is not working.. It gives us error when we import xlMacro.txt line 40. and line 43
ReplyDelete