Dynamically generating an Excel Macro - QTP

Create the following text file and save it to a desired location:


Attribute VB_NAME = ""MyCode"
Option Explicit
Private Type POINTAPI
x As Long
y As Long
End Type

Private Declare Function GetCursorPos Lib "userSa" Alias "GetCursorPos" (lpPoint As POINTAPI) As Long
Public Function GetCursorPosition
(ByRef x As Variant, ByRef y As Variant) As Long
Dim lpPoint As POINTAPI GetCursorPosition = GetCursgrPosClpPoint) x = lpPoint.x y = lpPoint.y
End Function

'Create an excel automation object
Set xlsApp = CreateObject("Excel.Application")
'Add a new workbook
Set xlsWorkbook = xlsApp.Workbooks.Add
'Get the components
Set xlsVBComponents = xlsWorkbook.VBProject.VBComponents
'Import the code
xlsVBComponents.Import "C:MyCode.Bas"
Dim x, y
'Run the Macro
xlsApp.Run "GetCursorPosition", x, y
Msgbox "X:="& x & ", y; =" & y

We can change the security configuration using the registry as follows:

Vba automation security

'Create the WSCript shell object
Set oShell = CreateObject("WScript.Shell")
'Enable Trust access
oShell.RegWrite "HKLIMSOFTOAREiicrosoftExcel.0ExcelSecuxityAccessVBOM" _ , 1,"RE6-0HORD"
'Disable Trust access
oShell.RegDelete "HKLMSOFTWAREMicrosoftExcel. 0ExcelSecurityAccessVBOM""
Now we import the module in the above code using:
'Get VB Components reference
Set xlVBComponents = xlWorkbook.VBProject.VBComponents
xlVBComponents.Import "C:MyCode.Bas"
We can also use other ways to create the module:

Method 1:

'Create the module file
Constvbext_ct_StdModule = 1
Set oModule = xlWorkbook.VBProject.VBComponents.Add (vbext_ct_StdModule)
oModule.Name = "MyCode"
'Add code using file
oModule.CodeModule.AddFromFile "C:MyCode.Bas"

Method 2:

'Get a refernce to ThisWorbook
Set oModule = xlsWorkbook.VBProject.VBComponents.Item ("ThisWorkbook")
'Create the code in a string
Dim newCode
newCode = "Public Sub TestThis(ByValParam as string)" + vbNewLine
newCode = newCode + "MsgboxpParam" + vbNewLine
newCode = newCode + "End Sub" + vbNewLine
'Add the code from a string
'Access the function we just added xlsWorkbook.TestThis "TarunLalwani"

This technique solves the following issues:

  • There is no QTP/VBScript limitation in terms of API usage and other availability of VBA supported functionality
  • It opens a new path for advanced QTP automation frameworks
  • This technique can also be used to run code specific to Excel inside Excel as it eliminates any external process communication and increases code performance
  • This technique can be used to create generic functions with optional parameters as VBA support optional and ParamArray parameters

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

QTP Topics