topic Re: Calling VBScript macro from loadscript in QlikView

Calling VBScript macro from loadscript

Wed, 04 Apr 2012 05:10:36 GMT

Hello All,

I have created macro in Edit Module using VBScript.

Now I want to call the same macro from EditScript.

Is this possible and if yes how?

Re: Calling VBScript macro from loadscript

Wed, 04 Apr 2012 06:11:27 GMT

Try this example:

the script:

T:
LOAD * INLINE [
    F1
    1
    2
    3
    4
    5
];

T2:
load *,test(F1) as F2 resident T;

drop table T

the vbs (macro):

function test (x)

test=x+1

end function

Re: Calling VBScript macro from loadscript

Wed, 04 Apr 2012 09:24:03 GMT

Hello Matan,

Thanks for you reply.

I tried above script. It gives error

Error in expression:

TEST is not a valid function

T2:

load *,test(F1) as F2 resident T

See I will explain u d functionality i need to achieve.

Basically after the qvw file gets reloaded, its PDF is to be created automatically for which i have written macro. I have set the action Run Macro on trigger OnPostReload and this works fine when I run the report on qlikview developer.

But when the same report is reloaded through qlikview server it does not fire the trigger after completion of reload.

I have also done the settings on server to allow macro execution. On qlikview community i found one more setting by pressing Ctrl+shift+m. I have also done that.

In spite of all these things macro is not getting executed on server. So i was trying if macros can be executed through edit script. I am using Qlikview 11.00 SR1 version and I dont have publisher.

Let me know if I have to do any other settings or anything else is required.

Please help on this matter ASAP. Its really very urgent.

Thank u in advance.

Re: Calling VBScript macro from loadscript

Alexander_Thor — Wed, 04 Apr 2012 09:42:23 GMT

You can execute macro functions by doing something like this in your load script:
LET Execute = MyMacroSub();

Where MyMacroSub is a function defined in your macro.

The hard part is that the macro will execute with the security of the user that runs your QVService, or Publisher if you had that. So this is a HUGE security risk as you would need to assign system access to the account running your services.

Re: Calling VBScript macro from loadscript

Wed, 04 Apr 2012 10:15:31 GMT

Hi Alexander,

Thanks for your reply.

I tried LET Execute = MyMacroSub(); (I put my macro name in place of MyMacroSub()) but it did not work.

Any other idea ???

Please reply ASAP.

Thank u so much.

Re: Calling VBScript macro from loadscript

Alexander_Thor — Wed, 04 Apr 2012 11:00:52 GMT

Gah sorry, bit tired over here. You need to define it as a function in the macro instead of a subroutine.

Try this test macro and you should be able to execute it from your load script, works on my qv11 machine atleast

Macro code:

Function CreateFolder() 
Dim objFSO 
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CreateFolder("C:\MacroTestFolder")
End Function

QlikView Load Script:

LET CreateFolder = CreateFolder();

Re: Calling VBScript macro from loadscript

Wed, 04 Apr 2012 11:33:56 GMT

Hi Alexander,

Thanks a lot for quick response. I really appreciate that.

These are the two functions I am using for auto PDF generation.

Function printRP01()

ActiveDocument.GetApplication.Sleep 2000

ActiveDocument.PrintReport "RP01", "PDF-Xchange 4.0", false

ActiveDocument.GetApplication.Sleep 20000

PrintReportPDF "MyReport"

end sub

'===========================================================================

Function PrintReportPDF(pdfOutputFile)

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "D:\PDFGenerate\Report1.pdf" , "D:\PDFGenerate\" & pdfOutputFile & ".pdf", True

End function

I tried what you have suggetsed in your above reply. But it did not execute printRP01() and also it did not give an error.

Can you plz brief what to do???

Re: Calling VBScript macro from loadscript

Thu, 12 Apr 2012 09:00:36 GMT

Hello,

Please can anyone tell why it did not execute printRP01 function, but did execute the PrintReportPDF function.

what is wrong in first function.

Re: Calling VBScript macro from loadscript

Miguel_Angel_Baeyens — Thu, 12 Apr 2012 09:04:51 GMT

Hi,

The code seems to be OK, but ActiveDocument is not available when called from the load script, since the document only "exists" when the script has completely finished. So any call to ActiveDocument functions will not work (is not a bug but a design) if they are called from the script. Rather, you need to end the script then use a button or something similar to call them.

As a workaround, you can use the qv.exe /r parameter and create a batch file that is triggered from the Windows Scheduler instead of the QlikView Server, and has an action OnOpen or OnPostReload that calls the macro instead.

Hope that makes sense and helps.

Miguel

Re: Calling VBScript macro from loadscript

Thu, 12 Apr 2012 09:09:38 GMT

Hi Miguel,

I did try for .bat file also.

But it gets hung when it executes the macro.

Re: Calling VBScript macro from loadscript

Miguel_Angel_Baeyens — Thu, 12 Apr 2012 09:28:21 GMT

Hi,

Make sure the macro works fine when it's called from the QlikView Desktop opened locally, and make sure that the paths (D: drive in your code) is available for the user running the BAT file. Check the Windows Event Viewer to see whether it's throwing any error to the system just in case.

Hope that helps.

Miguel

Re: Calling VBScript macro from loadscript

Thu, 12 Apr 2012 09:34:32 GMT

Hi Miguel,

macro works fine when called frm QV desktop.

Event viewer shows Application hang as an error.

Re: Calling VBScript macro from loadscript

Rich-HHE — Thu, 31 Jan 2013 20:12:05 GMT

I've tried to use the qv.exe /r method but it still doesn't trigger OnOpen or OnPostReload. Thoughts? QV 11 SR2

Nevermind, it's working ... sometimes and not consistently.