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:
Final Result when using the function IncludeAndExecute multiple times

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 …
Bookmark and Share

and tagged , , , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

16 Comments

  1. Posted January 15, 2010 at 13:15 | Permalink

    Great tip!

    Make more easy to organize macros script.

  2. paul
    Posted January 16, 2010 at 08:50 | Permalink

    Hi sir , I think you are trying to share with us how to use include how to org QV file. and i try to learn how to use this command. but after i down load your file which you attach in this post , i not sure how to open the 2 include file ? and i don’t knwo how to use it . can you pls share with me.

    http://community.qlikview.com/forums/t/24218.aspx

    Above link is one of the user try to also use include for org file. ( which i also not sure hwo to use it )

    One more question , may i know your blog allow you to do file attachment , how you do it , i like to start a blog same like you , can you share with me please .

    Paul

    • Posted January 16, 2010 at 13:07 | Permalink

      One more question , may i know your blog allow you to do file attachment , how you do it , i like to start a blog same like you , can you share with me please .Paul

      Hi Paul,

      I am using the WordPress blogging engine. All the features you need are there availble as a standard or by plugins. Just search for “WordPress” and you’ll find thousands of articles … :)

      Best regards
      Stefan

    • Posted January 16, 2010 at 13:39 | Permalink

      Hi sir , I think you are trying to share with us how to use include how to org QV file. and i try to learn how to use this command. but after i down load your file which you attach in this post , i not sure how to open the 2 include file ? and i don’t knwo how to use it . can you pls share with me.

      http://community.qlikview.com/forums/t/24218.aspx

      Above link is one of the user try to also use include for org file. ( which i also not sure hwo to use it )

      Paul

      Hi Paul again,

      some clarification.
      The method described in this article is used for encapsulation and organizing your macro code in a better way.

      What exactly did not work with my example.
      Maybe you could give me some more details. Then I will be glad to help you …

      Best regards
      Stefan

  3. Vladimir Sconza
    Posted January 16, 2010 at 14:35 | Permalink

    Hi Stefan,

    in your article there is a screenshot at the bottom of the article where you include some libraries like

    – ExcelHelper_v1.8.vbs
    – MailingLibrary.vbs
    – GUI_Library.vbs

    It would be nice if you could share these libraries to all :)

    V.

    • Posted January 18, 2010 at 08:19 | Permalink

      Hi Vladimir again,

      yes, I will share those libraries. But at the moment there are many other articles in the pipeline so it will take some time until there is a slot free for publishing these libraries :)

      In the meantime: If there is something special you are looking for, just drop me an eMail and I’ll send you a part of these libraries … :)

      Best regards
      Stefan

  4. paul yeo
    Posted January 19, 2010 at 07:41 | Permalink

    Hi Stefan,
    Thank you for post the QV doc file at share Qlikview , I will try to download and figure that is the include try to do ? By the way thank you for your info on word press . I will start one soon.
    Just like to confirm if i want to use include command for directory folder name , it is possible ? still confuse on this power full command,.
    Paul

    • Posted January 19, 2010 at 13:02 | Permalink

      Hi Paul,

      what do you mean with “use include command for directory folder name”?

      Do you mean that you would like to include all files (containing) macros which are located under a specific directory?

      If you, yes sure, this would be possible: You would just have to create a macro for iterating all files within a folder and running the IncludeAndExecute command described in the article for every file.

      Hope this helps!
      Best regards
      Stefan

  5. paul
    Posted January 21, 2010 at 14:26 | Permalink

    Hi sir ,
    Thank you for your reply.
    Paul

  6. Posted February 2, 2010 at 07:01 | Permalink

    nice post. thanks.

  7. Brian Garland
    Posted November 9, 2011 at 21:17 | Permalink

    Stefan, did you ever post those VBS libraries you mention in this article?

  8. Posted February 2, 2013 at 05:52 | Permalink

    hello!,I love your writing very so much! percentage we keep up a correspondence more
    approximately your article on AOL? I require a specialist on this house to unravel
    my problem. May be that is you! Taking a look forward
    to see you.

  9. Posted February 2, 2013 at 06:32 | Permalink

    Hey are using WordPress for your site platform? I’m new to the blog world but I’m trying to
    get started and create my own. Do you need any coding knowledge to make your own blog?
    Any help would be greatly appreciated!

  10. alex
    Posted May 20, 2014 at 11:29 | Permalink

    Hi interesting post!!!!! by the way would be possible to place the vbs in a webpage?

    • Posted May 24, 2014 at 22:14 | Permalink

      Theoretically yes, but would not be supported by many browsers and you would run into security-troubles, so i fact: NO …

      Regards
      Stefan

4 Trackbacks

  1. […] QlikTip #17: Simulating the $(include) command in QlikView macros […]

  2. […] Possibly posts:QlikTip #17: Simulating the $(include) command in QlikView macros […]

  3. […] QlikTip #17: Simulating the $(include) command in QlikView macros […]

  4. […] QlikTip #17: Simulating the $(include) command in QlikView macros […]