Calling VB script macro from load script

[#1]

Hi all,

This problem is so frustrating and I am sure there is an easy answer...

I have created a vb macro that performs a soap call and then writes the xml that is returned to a file. I can run this from the  front end with a button that activates the macro and everything works just fine.

However when i try and call the script using Let loadfile = MyVBMacro();  in the load script nothing happens!

Is there a security setting I'm missing?

Please help!

Mark

[luciancotea]

You can call only functions, not subroutines. Change from

SUB MyVBMacro

...

END SUB

to

FUNC MyVBMacro()

...

END FUNC

[#3]

Hi Lucian,

Thanks for your reply

It is constructed as a function already. Any other ideas?

Mark

[luciancotea]

Here's a working example of calling macro function from script.

[#5]

Thanks Again Lucian, but still no joy. Any ideas if you see my code with just the soap addresses removed?

Below is my script

Load script

let getRA = wsSOAP_RA();

I have 3 variables :

getRA

vXMLRecieved

vXMLSent

The VB script:

function wsSOAP_RA()

'Confect xml to send

        xmlToSend = ""

xmlToSend = xmlToSend & "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:soap='http://XXXXXXXXXXXXXXXXXXXXXXXXXXXX:1'>"& vbCr & vbLf

xmlToSend = xmlToSend & "<soapenv:Header>"& vbCr & vbLf

xmlToSend = xmlToSend & "<soap:appid>test</soap:appid>"& vbCr & vbLf

xmlToSend = xmlToSend & "</soapenv:Header>"& vbCr & vbLf

xmlToSend = xmlToSend & "<soapenv:Body>"& vbCr & vbLf

xmlToSend = xmlToSend & "<urn:findAllResearchAreas>"& vbCr & vbLf

xmlToSend = xmlToSend & "</urn:findAllResearchAreas>"& vbCr & vbLf

xmlToSend = xmlToSend & "</soapenv:Body>"& vbCr & vbLf

xmlToSend = xmlToSend & "</soapenv:Envelope>"& vbCr & vbLf



'Record xml sent to QV variable

set txt1 = ActiveDocument.Variables("vXMLSent")

txt1.setContent xmlToSend, true



'Create xmlhttp opject

Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

xmlhttp.open "POST", "http://XXXXXXXXXXXXXXX/Organisation_Services/ResearchAreaInteraction/1", false

'xmlhttp.open "POST", "http://localhost:8080/Organisation_Services/ResearchAreaInteraction/1", false





xmlhttp.setRequestHeader "Content-Type", "text/xml;charset=UTF-8"

xmlhttp.setRequestHeader "SOAPAction", "XXXXXXXXXXXXXXX:1:findAllResearchAreas"

xmlhttp.send xmlToSend



result = xmlhttp.responseText     

result = replace(result, "&gt;", ">")

result = replace(result, "&lt;", "<")

set txt2 = ActiveDocument.Variables("vXMLReceived")

txt2.setContent result, true

' Set folder name where file will be saved



foldername = "G:\Documents\Qlikview scripts\"



' Set file name where data will be stored



filename = "xmlReponse.xml"



Set objFSO = CreateObject("Scripting.FileSystemObject")



' Check if folder exists else create it



If objFSO.FolderExists(foldername) Then



Set objFolder = objFSO.GetFolder(foldername)



Else



Set objFolder = objFSO.CreateFolder(foldername)



End If



' Check if file exists else create it



If objFSO.FileExists(foldername & filename) Then



Set objFolder = objFSO.GetFolder(foldername)



Else



Set objFile = objFSO.CreateTextFile(foldername & filename)



End If



set objFile = nothing



set objFolder = nothing



' Constant that indicate that file content will be overwritten and not appended



Const ForAppending = 2



' Open the text file



Set objTextFile = objFSO.OpenTextFile (foldername & filename, ForAppending, True)



' And paste the content



objTextFile.WriteLine(result)



objTextFile.Close



end function

[luciancotea · Accepted solution]

Maybe it's because you call some elements from the document which are not available at load time.

[#7]

Thanks for the point Lucian - yes I stripped out the variables vXMLSent and vXMLReceived and its working fine. Thanks for your help!

Mark

[#8]

I am trying to get a macro to execute from the Load Script and it is not working.  Can anyone see what is wrong with the Macro code which prevents this from executing in the Load Script?  It works fine when executing the macro via a button.

FUNCTION SOAP_REQUEST

    set rel_expr_val = ActiveDocument.Variables("vRelevanceExpression")

    msgbox("Test")

  xmlToSend = ""

  xmlToSend = xmlToSend & "<?xml version='1.0' encoding='utf-8'?>"

  xmlToSend = xmlToSend & "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'  xmlns:xsd='http://www.w3.org/2001/XMLSchema'  xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>"

  xmlToSend = xmlToSend & "<soap:Body>"

  xmlToSend = xmlToSend & "<GetRelevanceResult xmlns='http://TEST/webreports'>"

  xmlToSend = xmlToSend & "<relevanceExpr><![CDATA["&rel_expr_val.GetContent.String&"]]></relevanceExpr>"

    xmlToSend = xmlToSend & "<username>qlikview</username>"

    xmlToSend = xmlToSend & "<password>qlikview</password>"

  xmlToSend = xmlToSend & "</GetRelevanceResult>"

  xmlToSend = xmlToSend & "</soap:Body>"

  xmlToSend = xmlToSend & "</soap:Envelope>"

  set txt1 = ActiveDocument.Variables("vXMLSent")

  txt1.setContent xmlToSend, true

  'Create xmlhttp opject

  Set xmlhttp = CreateObject("Microsoft.XMLHTTP")

  xmlhttp.open "POST", "http://TEST/webreports", false

  xmlhttp.setRequestHeader "Content-Type", "text/xml"

  xmlhttp.setRequestHeader "SOAPAction", "http://TEST/webreports"

  xmlhttp.send xmlToSend

  result = xmlhttp.responseText

  result = replace(result, "&gt;", ">")

  result = replace(result, "&lt;", "<")

'    Path = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")

    Set FileDestVar = ActiveDocument.Variables("vFldrDataExtracts")

    FileName = FileDestVar.GetContent.String&"\Test.xml"

  set fso = CreateObject("Scripting.FileSystemObject")

    set s = fso.CreateTextFile(FileName, True)

    s.writeline(result)

    s.Close()

  ActiveDocument.DoReload 2, true

  set txt2 = ActiveDocument.Variables("vXMLReceived")

END FUNCTION

[#9]

hi,

   I want to pass  value from macro function to script ,on cliking the button.Later that value will be used in load statement .

In macro I wrote the code like this.The variable fxV is need to be passed in script.

In script 

I want to use the selected value from the list box in WHERE clause.Then the result alone will be saved in qvd file.

When Im doing like this  I got the error like this.

Please help me in solving this

[luciancotea]

Can't call ActiveDocument elements at LOAD time. They are not constructed yet.