How to add (create) Excel macro from QTP
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
Set XLHandle = CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
Set objCodeModule = XLBook.VBProject.VBComponents.Item("Module1").CodeModule
sNewCode = "Public Function NewFunction(ByVal intStartRow, ByVal intEndRow)" & vbCrLf
sNewCode = sNewCode &" Sheet1.Range(Sheet1.Cells(intStartRow, 1), Sheet1.Cells(intEndRow, 10)).Select" & vbCrLf
sNewCode = sNewCode &" Selection.Interior.ColorIndex = 35" & vbCrLf
sNewCode = sNewCode &"End Function" & vbCrLf
objCodeModule.AddFromString(sNewCode)
XLBook.Save
XLBook.Close
XLHandle.Quit
Set objCodeModule = Nothing
Set XLBook = Nothing
Set XLHandle = Nothing
