Within load-scripts in QlikView there is the useful “$(include)
” command available for including files containing some script to be used within the load-script.
Doing so it is easy to encapsulate and reuse some code used in several QlikView applications.
So organizing your code in load-scripts is easy, you can use several tabs and the $(include)
command, whereas QlikView is not offering similar possibilities in the macro-editor. There are no tabs and “officially” there does not exist an corresponding command for including code in macros.
But that’s not the whole story, there is a nice possibility:
QlikTech is using the VBScript engine for interpreting the macro code by default (you could also use the JavaScript engine …). In VBScript there is a not very well known command called “ExecuteGlobal
” (Official reference at MSDN, which “executes one or more specified statements in the global namespace of a script.”
So can we use that in QlikView macros?
Yes, we can!
Dim objFSO 'as Object Dim objFile 'as Object Dim strScript 'as String '// Open the File using File-System-Objects Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("SOME_SCRIPT_TO_ENCLUDE.vbs") '// Copy the content of the file strScript = file.ReadAll '// Just destroy the objects again Call objFile.Close() '// Execute the script globally Call ExecuteGlobal(strScript)
Doing so at the beginning of the script you can now use the content defined in SOME_SCRIPT_TO_ENCLUDE.vbs
in the subsequent macro code.
Because I am using this possibility of including existing scripts quite often I have improved the code above a little bit and created a function which I use in every application where I need this stuff:
'// ***************************************************************** '// Function for executing external content in your application. '// ~ '// Parameters: '// p_strFilePath – full absolute path in relation to the QlikView application '// ~ '// Return Value: '// Will return true if succeeded, otherwise false (e.g. if the file '// does not exist) '// Examples: '// IncludeAndExecute(“D:\QlikView\Scripts\FileFunctions.vbs”) '// Further information: http://www.qlikblog.at/509/ '// ****************************************************** Private Function IncludeAndExecute(ByVal p_strFilePath) 'as Boolean '// Variable declaration Dim objFSO 'as Object Dim objFile 'as Object Dim strScript 'as String '// Open the File using File-System-Objects Set objFSO = CreateObject("Scripting.FileSystemObject") '// prevent errors ... On Error Resume Next Set objFile = objFSO.OpenTextFile(p_strFilePath) '// Copy the content of the file strScript = objFile.ReadAll '// Just destroy the objects again Call objFile.Close() '// Execute the script globally Call ExecuteGlobal(strScript) '// If an error occurred just return false If (len(Err.Description) > 0) Then IncludeAndExecute = false '// Just comment the following line if you want to suppress messages '// in case of errors msgbox(Err.Description) Exit Function End If On Error Goto 0 IncludeAndExecute = true End Function
And then I am using this code as follows:
For testing purposes you can download all the scripts and a sample application by clicking on the link below:
Related downloads:
Advantages
- Code reuse and code encapsulation is easier
- Because you are now only using text-files integration into CVS-systems will be easier!
- Because of using the File-System-Objects (FSO) you have to enable “System Access” in the macro-security settings!!!
Disadvantages
- You are creating an unnecessary, artificial dependency between the included scripts and your QlikView-applications! So certainly you have to take care when changing your global scripts …
16 Comments