12 Replies Latest reply: Apr 2, 2014 12:36 PM by Lucian Cotea RSS

    Calling VB script macro from load script

      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
          Lucian Cotea

          You can call only functions, not subroutines. Change from

           

          SUB MyVBMacro

          ...

          END SUB

           

          to

           

          FUNC MyVBMacro()

          ...

          END FUNC

          • Re: Calling VB script macro from load script

            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



             

            • Re: Calling VB script macro from load script
              Nilesh Tailor

              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