topic Re: Calling VB function in load script in QlikView

Calling VB function in load script

Wed, 18 Sep 2013 16:53:48 GMT

Hello Qlikview Community,

I could really use someone's help here. How do I go about calling a function written in VB in the QV loadscript?

This is the situation:

Each week, we refresh our qlikview dashboard with a new dataset. We have automated this refresh and each week our dashboard looks for a file of the same name (ie. NewData). What we are attempting to do is after the file "NewData" is loaded, we want to move it to a different folder and attach the date to it.

I have written a VB script that successfully renames and moves a file. That code is below:

Sub RenameMoveFile()

Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

FSO.MoveFile "C:\Desktop\Test\NewData.txt", "C:\Desktop\Moved\MovedFile.txt"

Set FSO = nothing

End Sub

What I'm having issues with is how to call this function in the load script. I've tried both "Call RenameMoveFile()" and "Let v_RMF = RenameMoveFile()" which have been popular solutions in this forum. I also have already ensured the my requested module security is set to System Access and my current local security is set to Allow System Access.

Any ideas on how to make this work or new solutions would be greatly appreciated!

Thanks,

Alex

Re: Calling VB function in load script

Oleg_Troyansky — Wed, 18 Sep 2013 17:05:48 GMT

I believe you need to use the function call inside a load statement - even a dummy load with "autogenerate 1".

As an alternative, however, I'd recommend using a statement EXECUTE that allows running an external command. Something like this:

EXECUTE cmd.exe /C xcopy "D:\MyFolder\MyFile.txt" "D:\NewFolder" /y;

You can probably do it in a similar way with a .BAT file that executes more than a single command at a time.

cheers,

Oleg Troyansky

www.masterssummit.com

Re: Calling VB function in load script

Wed, 18 Sep 2013 17:37:10 GMT

Hi Oleg,

Thanks for the reply. I have written the VB script within the Edit Module in Qlikview and external commands will not be an option as I do not have control over the server in which our solution is hosted.

Could you expand more on your comment "function call inside a load statement - even a dummy load with "autogenerate 1"."?

Thanks!

Alex

Re: Calling VB function in load script

MayilVahanan — Wed, 18 Sep 2013 17:51:48 GMT

Try like this

Is there any possibility to call a Macro in the ‘Edit Script’?

Re: Calling VB function in load script

Wed, 18 Sep 2013 17:59:22 GMT

Hello Mayil,

Thanks for your response. The only issue with that solution is my code is written in VB and cannot be simply inserted into the subroutine as suggested.

Sub macroname ()

insert code here....

End sub

Call macroname()

Unless you know of someway within the edit script that I can temporarily write VB?

Or do you know of a way I can rename/move files within the capabilities of QV code?

Thanks,

Alex

Re: Calling VB function in load script

MayilVahanan — Wed, 18 Sep 2013 18:06:33 GMT

I think , this will answer for your question

call sub from script issue

Re: Calling VB function in load script

Wed, 18 Sep 2013 19:19:46 GMT

Hi All,

Thank you all for the responses. Here is my solution:

//In Edit Module:

Function RenameMoveFile()

Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

FSO.MoveFile "C:\Desktop\Test\NewData.txt", "C:\Desktop\Moved\MovedFile.txt"

Set FSO = nothing

End Function

//In Edit Script:

Load RenameMoveFile();