topic Re: Calling VB script macro from load script in QlikView

Calling VB script macro from load script

Wed, 17 Jul 2013 12:12:29 GMT

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

Re: Calling VB script macro from load script

luciancotea — Wed, 17 Jul 2013 12:29:54 GMT

You can call only functions, not subroutines. Change from

SUB MyVBMacro

...

END SUB

FUNC MyVBMacro()

...

END FUNC

Re: Calling VB script macro from load script

Wed, 17 Jul 2013 12:36:42 GMT

Hi Lucian,

Thanks for your reply

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

Mark

Re: Calling VB script macro from load script

luciancotea — Wed, 17 Jul 2013 12:40:01 GMT

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

Re: Calling VB script macro from load script

Wed, 17 Jul 2013 13:04:10 GMT

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, ">", ">")

result = replace(result, "<", "<")

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

Re: Calling VB script macro from load script

luciancotea — Wed, 17 Jul 2013 13:46:50 GMT

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

Re: Calling VB script macro from load script

Wed, 17 Jul 2013 14:47:25 GMT

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

Mark

Re: Calling VB script macro from load script

Mon, 23 Dec 2013 16:09:05 GMT

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, ">", ">")

result = replace(result, "<", "<")

' 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

Re: Calling VB script macro from load script

Tue, 01 Apr 2014 12:12:41 GMT

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

Re: Calling VB script macro from load script

luciancotea — Wed, 02 Apr 2014 06:13:20 GMT

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

Re: Calling VB script macro from load script

luciancotea — Wed, 02 Apr 2014 06:15:14 GMT

You want to call ActiveDocument.Fields() at LOAD time. It's not available at that time.

Re: Calling VB script macro from load script

Wed, 02 Apr 2014 09:09:12 GMT

hi,

can you please mention where the ActiveDocument.Fields() need to be used?

Re: Calling VB script macro from load script

luciancotea — Wed, 02 Apr 2014 16:36:27 GMT

I didn't said you need, I said you want to call ActiveDocument.Fields() at LOAD time. You can't do that because ActiveDocument.Fields() does not exists at LOAD time.