2

I am trying to execute a function from a functional library (which I am loading at runtime). Everything works if I use Eval or Execute to run the function but I wanted to use GetRef as mentioned in this and this

QTP Code Snippet

Call LoadFunctionLibrary("/../Libraries/Tests.vbs")
Set objCon = CreateObject("ADODB.Connection")
objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & "C:\_Work\WorkingFolder\TestList.xlsx" & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"
Set objRS = objCon.Execute("Select * from [Sheet1$] Where [To Execute] = 'YES'")
Do While Not objRS.EOF
     'Eval( objRS.Fields(1).Value)
     'Execute objRS.Fields(1).Value

     Set tcFunc = GetRef(objRS.Fields(1).Value)
     tcFunc
    objRS.MoveNext
Loop

In /../Libraries/Tests.vbs file, I have this function

Sub foo()
  msgbox "hello"
End Sub

which is referenced in the Excel as below

enter image description here

When I try to use GetRefm QTP is throwing the below error

Invalid procedure call or argument: 'GetRef' Line (10): "Set tcFunc = GetRef(objRS.Fields(1).Value)".

If foo is defined in QTP's Action, then it works but when I import it from a functional library, it fails. Any idea how to make it work?

EDIT -- WORKAROUND SOLUTION

Instead of using LoadFunctionLibrary, use ExecuteFile to load the functional library.

Community
  • 1
  • 1
Pankaj Jaju
  • 5,371
  • 2
  • 25
  • 41
  • Would that not be pulling the headings?, so in effect, it's trying `GetRef("Test Name")` and failing *(for multiple reasons, 1. procedure pointers can't contain spaces 2. it doesn't exist)*. Have you actually tried debugging what `objRS.Fields(1).Value` is returning rather than assuming? – user692942 Jan 25 '17 at 13:13
  • `HDR=YES` so it will treat headers correctly. Also, `objRS.Fields(1).Value` correctly prints the value (i used `print` just to verify) – Pankaj Jaju Jan 25 '17 at 13:15
  • Why the loop through, you only want to call `GetRef()` once right? – user692942 Jan 25 '17 at 13:17
  • This is just a test code that I pasted ... in reality I am expecting multiple tests mentioned in the excel file and hence I would want to execute all of them if To Execute is set to True – Pankaj Jaju Jan 25 '17 at 13:18
  • As a test move `foo()` out of the external vbs file and place it inside the test code, does it still fail? I suspect it might be to do with how `LoadFunctionLibrary()` loads the vbs, does it use `Execute()`? – user692942 Jan 25 '17 at 13:20
  • Surprisingly no ... it behaves weirdly if I am trying to GetRef from a different file/action. I also think it is some bug in QTP's `LoadFunctionLibrary` but not sure – Pankaj Jaju Jan 25 '17 at 13:21
  • I think I have your answer, this is a duplicate question. See [How to call a function (with parameters) which is in function library, taking the function name from a variable?](http://stackoverflow.com/q/16691353/692942) – user692942 Jan 25 '17 at 13:23
  • Suspect that your `LoadFunctionLibrary()` procedure doesn't use `ExecuteGlobal` to execute the external vbs meaning it isn't available to global scope so `GetRef()` will never see it. – user692942 Jan 25 '17 at 13:25
  • Ahh .. yeah same question and unfortunately my suspicion is correct. QTP's fault - might have to go with `Eval` only. `LoadFunctionLibrary` is QTP's inbuilt function to import external code ... probably something fishy in there. Thanks. – Pankaj Jaju Jan 25 '17 at 13:26
  • 3
    @Lankymart - Just used `ExecuteFile` instead of `LoadFunctionLibrary` and that did the trick. `LoadFunctionLibrary` is a recent development in QTP so assuming that it is not fully tested (which is ironic given QTP is a testing tool) – Pankaj Jaju Jan 25 '17 at 13:33
  • Add an answer then, that way the question is resolved. It's perfectly fine to answer your own question as the question and answer are for everyone. – user692942 Jan 25 '17 at 13:35
  • @Lankymart - Can't answer because its not duplicate. Adding it in the question itself. – Pankaj Jaju Jan 25 '17 at 13:38
  • Instead of adding to the question *(as that will likely be edited out by someone)* add your answer *(maybe even give a little context)* to the highlighted duplicate question. I think your answer would still be relevant there. – user692942 Jan 25 '17 at 13:39
  • Not getting the answer option when I am editing the question. Looks like i need to wait 48 hours before i can answer. Why don't you add the answer (if you can) and I will accept it so that future audience can see the working solution. – Pankaj Jaju Jan 25 '17 at 13:42
  • You don't need to edit the question, just open the question link and at the bottom of the page you should see "Your Answer" with a text box. – user692942 Jan 25 '17 at 13:43
  • 1
    I can't see any answer box - looks like I forgot to check Answer my own question checkbox. – Pankaj Jaju Jan 25 '17 at 13:47

0 Answers0