QlikTip #17: Simulating the $(include) command in QlikView macros

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 …

Possibly related posts:

1. QlikTip # 18: A workaround for passing parameters to QlikView-macros
   When calling macros from the user interface you cannot pass a parameter to the function called in QlikView. This behavior is quite annoying …! But the workaround explained here will show you a possibility how...
2. QlikTip #19: Suppressing Macro-Security (Module Security) Dialog on QlikView-Server/QlikView-Documents
   When opening documents with macros the end-user will be shown a dialog to define the desired macro-security/module security (in the QlikView Windows Client or the QlikView IE Plugin): But what, If you do not want...
3. QlikTip #22: Restarting/Stopping/Starting services in QlikView 9
   Even if already published on another Qlikview related blog I just wanted to have this here because I am using it quite often. For starting/stopping/restarting the QlikView Windows-services of QlikView server in version 9 I...
4. QlikTip #20: Why we do not need a SELECT-CASE/Switch-Case in QlikView load-statements …
   I have recently received the question why there is no SELECT-CASE or SWITCH statement available in QlikView within load-statements. Sure, if you are looking into the reference-manual or into the help file, you’ll find the...