QlikView & Business-Intelligence Know-How from Vienna/Austria.

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:

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 …