Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Calling VBScript macro from loadscript

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?

12 Replies
Not applicable

Re: Calling VBScript macro from loadscript

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

Not applicable

Re: Calling VBScript macro from loadscript

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.

Employee
Employee

Re: Calling VBScript macro from loadscript

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.

Not applicable

Re: Calling VBScript macro from loadscript

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.

Employee
Employee

Re: Calling VBScript macro from loadscript

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();

Not applicable

Re: Calling VBScript macro from loadscript

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???

Not applicable

Re: Calling VBScript macro from loadscript

Hello,

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

what is wrong in first function.

MVP
MVP

Re: Calling VBScript macro from loadscript

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

Not applicable

Re: Calling VBScript macro from loadscript

Hi Miguel,

I did try for .bat file also.

But it gets hung when it executes the macro.

Community Browser