Strange behaviour of VBS macro

[#1]

While trying to get access to files in the file system of our QlikView server from within a QlikView application on the server using VBS I am hindered by the following phenomenon.

I start out creating a file system object and then do a check if the folder where the files are supposed to be exists. This works outside of the QVS but not when I do the same from inside the QVW.

I set both security settings in the macro editor dialogue to "system access" (or the equivalent of that in German).

VBS Script (run from desktop):

Option Explicit
'Test FSO
Public Function TestFSO()
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 If IsObject(objFSO) Then
 TestFSO = "object"
 Else
 TestFSO = "no object"
 End If
Set objFSO = Nothing
End Function
'Test Folder
Public Function TestFolder(path_to_folder)
 Dim varPath
 varPath = path_to_folder
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 If objFSO.FolderExists(varPath) Then
 TestFolder = "folder exists"
 Else
 TestFolder = "folder does not exist"
 End If
Set objFSO = Nothing
End Function
Dim s1
Dim s2
s1 = TestFSO()
s2 = TestFolder("C:")
WScript.echo(s1)
WScript.echo(s2)

... gives:

QVW script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
LET varFSO = TestFSO();
LET varFolder = TestFolder("C:");

QVW Module:

Option Explicit
'Test FSO
Public Function TestFSO()
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 If IsObject(objFSO) Then
 TestFSO = "object"
 Else
 TestFSO = "no object"
 End If
Set objFSO = Nothing
End Function
'Test Folder
Public Function TestFolder(path_to_folder)
 Dim varPath
 varPath = path_to_folder
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 If objFSO.FolderExists(varPath) Then
 TestFolder = "folder exists"
 Else
 TestFolder = "folder does not exist"
 End If
Set objFSO = Nothing
End Function

...gives:

Any ideas why the VBS from inside the QVW does not work?

Thanks!

[#2 · Accepted solution]

Hi JenKue,

this is a real strange behaviour on the first look. I figured out, that there must be problem with passing of the parameter "C:\" out of the load script to the vbscript.

Add this Function to the QVW module:

Public Function TestFolderWrap()
 TestFolderWrap = TestFolder("C:\")
End Function

And change the variable assigenment in your load script to the new function:

LET varFolder = TestFolderWrap();

Does anybody know why this is not working as expected?

Helmut

[#3]

Since the passing of parameter is not possible if you call a macro from the user interface, I guess the same reason aplies for the load script.

Found this blog entry from Stefan Walther: http://www.qlikblog.at/558/qliktip-18-workaround-passing-parameters-qlikviewmacros/

[#4]

Helmut,

thanks for pointing this out to me. That was extremely helpful.

I did some looking into the passing of variables via function call/parameters from the script.

'Test passing of variables/parameters
Public Function TestPass(variable)
 TestPass = variable
End Function

LET varPass = TestPass("foo");
LET varPass2 = TestPass('foo');
LET varPass3 = TestPass(123);

Output (from QVW script debugger):

varPass3 123
varPass2 "foo"
foo <NULL>
varPass <NULL>

The "foo <NULL>" part between varPass and varPass2 is interesting. I have no idea what's going on with the script interpreter there. More interesting still is that varPass is Null while varPass2 is "foo". It seems that you have to enclose strings in single quotes when calling VBS functions from script, while double quotes do not work.

I did not know that, but I am still a beginner with QlikView. Case solved, I guess.

[#5]

At first I'm glad that it is possible to pass parameters to a vbscript function with single quotes

The output of the debugger is really interesting and pointed me to possible explanation.

The reason for this line is the command "LET". The command evaluates the part on the right side of the equal sign and assign the result to the variable.

LET interpretes the text TestPass("foo") in two steps. The first step is ("foo"), the second one is TestPass. TestPass got the result from the first step passed as parameter.

So what does ("foo") ? It tries to get the value from the variable with the name foo and since this variable does not exist, it returns NULL. TestPass itself returns the NULL and varPass gets NULL.

Regarding your first post, if you create a variable "C:\" with a value of "C:\" your inital script would work. Sounds a little bit crazy but in a specific way I like it