topic Re: VBScript to JScript - Help in converting! in QlikView

VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 12:42:01 GMT

Can you please help me here with one more please in converting from VBScript to JScript -

sub Export

vExportTime = Year(Now()) & Right("0" & Month(Now()), 2) & Right("0" & Day(Now()), 2) & " " & Right("0" & Hour(Now()), 2) & Right("0" & Minute(Now()), 2) & Right("0" & Second(Now()), 2)

set obj = ActiveDocument.GetSheetObject("LB98")

obj.ServerSideExportEx "C:\Users\dicky.mohanty\Documents\Bookmark Documents\Export_"&vExportTime&".qvd" , ";" , 4 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF

end sub

Also - Is it possible to use Relative Path/ Registry Path (for exporting into QVD) in Macro too, so as not to change them every time when we change environments from DEV to STG to PROD?

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 16:23:41 GMT

You can write this in JScript like this:

var dt = new Date();
var m = dt.getMonth()+1;
var d = dt.getDate();
var hh = dt.getHours();
var mm = dt.getMinutes();
var ss = dt.getSeconds();

var vExportTime2 = dt.getYear()+((m<10?'0':'')+ m)+((d<10?'0':'')+d)+' '+((hh<10?'0':'')+hh)+':'+((mm<10?'0':'')+mm)+':'+((ss<10?'0':'')+ss);

or this if you want a general padding function:

function padNum( n , separator ) {
  return ((n<10?'0':'') + n) + (separator==undefined?'':separator)
};

var dt = new Date();
var m = dt.getMonth()+1;
var d = dt.getDate();
var hh = dt.getHours();
var mm = dt.getMinutes();
var ss = dt.getSeconds();
var vExportTime1 = dt.getYear()+padNum(m)+padNum(d,' ')+padNum(hh,':')+padNum(mm,':')+padNum(ss);

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 16:29:22 GMT

Now I understand petter.skjolden‌,

Thanks Much.

I am Setting a Path Variable (which is already in my script to store the QVD like this ), but failing here -

vRootFileLocation and vQVDs are already predefined in the Load Script and bringing in below.

Set vRootFileLocation = Activedocument.Variables("vRootFileLocation")

Set vQVDs = Activedocument.Variables("vQVDs")

obj.ExportEx (vRootFileLocationvQVDs&"Export_"&vDocName&vExportTime&".qvd" , ";" , 4)

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 17:11:50 GMT

HI petter.skjolden‌ , petter-s‌

I changed the format to support in JScript, but still its failing here - -

obj.ExportEx ('vRootFileLocation\A.qvd', '4')

I have defeined the Variable like this -

var vRootFileLocation = ActiveDocument.Variables("vRootFileLocation")

obj = ActiveDocument.GetSheetObject("LB_Bookmark");

obj.ExportEx ('vRootFileLocation\A.qvd', '4')

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 18:11:06 GMT

var doc = ActiveDocument;
var vQVDs = doc.GetVariable('vQVDs').GetContent().String;
obj = doc.GetSheetObject('CH01');
obj.ExportEx(vQVDs + 'myQVD.QVD' , 4 );
// Can be used on both a server - if the QVW is run on a server - and on a client if run locally with QlikView Desktop
obj.ServerSideExportEx( vQVDs + 'myQVD2.QVD' , '' , 4 );

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 18:19:22 GMT

Thank you petter.skjolden‌ , petter-s‌

I tried like this below -

var doc = ActiveDocument;

var vRootFileLocation = doc.GetVariable('vRootFileLocation').GetContent().String;

var vQVDs = doc.GetVariable('vQVDs').GetContent().String;

obj = doc.GetSheetObject('LB_Bookmark');

obj.ExportEx(vQVDs + 'Exp.QVD' , 4 );

But getting error at last line - Object doesn't support this property or method

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 18:25:18 GMT

You have to have a variable called vQVDs in your QlikView application already defined. This variable also has to return a string. The string has to end with a backslash. This code works well for me...

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 18:33:55 GMT

Thank you petter.skjolden‌ , petter-s‌

I already have two predefined variables in my Script -

LET vRootFileLocation = GetRegistryString('HKEY_LOCAL_MACHINE\SOFTWARE\QlikView','RootFolderLocation');

Let vQVDs = '\QVDs\';

Still trying, if I am missing anything.

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 18:48:58 GMT

What exactly does the vRootFileLocation contain?

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 18:58:11 GMT

petter.skjolden‌ , petter-s‌,

vRootFileLocation is the Server Path Name, that we are storing in Registry Files to be consistent across Environments (DEV,STG,PROD)

vRootFileLocation = \\clientnetwork.net\Applications\OH001\QlikView\DataFilesStg\QlikView_Files\

vQVDs = '\QVDs\'

so finally, my QVD to be exported to -

\\clientnetwork.net\Applications\OH001\QlikView\DataFilesStg\QlikView_Files\QVDs\

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 19:50:19 GMT

I am not sure if ExportEx accepts UNC-paths in a file name at all. Try to save to a drive letter instead and see how that works....

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 20:00:38 GMT

petter-s‌,

However the ServerSideExportEx (using VBScript) was successfully accepting the UNC Path and was storing.

Something strange and weird here it seems -

Its not storing , when I am using like this too --

obj = doc.GetSheetObject("LB_Bookmark");

obj.ExportEx('C:\Users\pa-dicky.mohanty\Documents\Exp.QVD',4);

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 20:38:11 GMT

Hey petter-s‌ , petter.skjolden‌

Just to let you know -

UNC Path is accepted and now its storing the QVD perfectly.

However, can you please help - how to store the QVD in a specific format? I want to append the values of the vDocName and vExportTime to the QVD and store them to make the QVDs unique.

Wanted to store QVD as Export_ExportDocument_20160405 165021.qvd

function Export() {

var dt = new Date();

var m = dt.getMonth()+1;

var d = dt.getDate();

var hh = dt.getHours();

var mm = dt.getMinutes();

var ss = dt.getSeconds();

var vExportTime = dt.getYear()+((m<10?'0':'')+ m)+((d<10?'0':'')+d)+' '+((hh<10?'0':'')+hh)+':'+((mm<10?'0':'')+mm)+':'+((ss<10?'0':'')+ss);

var doc = ActiveDocument;

var vDocName = doc.GetVariable('vDocName').GetContent().String;

var vRootFileLocation = doc.GetVariable('vRootFileLocation').GetContent().String;

var vQVDs = doc.GetVariable('vQVDs').GetContent().String;

obj = doc.GetSheetObject("LB_Bookmark");

obj.ServerSideExport( vRootFileLocation + vQVDs + 'Export_"&vDocName&vExportTime&".qvd',4);

};

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 21:31:18 GMT

Yes that should work well. But note that Javascript (JScript) does NOT use the & as a string concatenation operator. In Javascript you must use the + operator to concatenate strings.

obj.ServerSideExport( vRootFileLocation + vQVDs + 'Export_' + vDocName + vExportTime + '.qvd',4);

And you should not mix quote characters. Just use single quotes or just use double quotes. In Javascript it is optional which you use. In VBScript you have to use double quotes.

I prefer to always use single quotes with Javascript.

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 21:37:57 GMT

Well petter-s, petter.skjolden‌

I tried a lot of Permutations and Combinations - and had already tried using the same way, as you have mentioned (either single quote or double quote).

When I am using the variable vDocName - quickly it is getting stored as desired.

obj.ServerSideExport( vRootFileLocation + vQVDs + 'Export_' + vDocName + '.qvd' , 4) ;

When I am using the variable vExportTime - it is taking time to execute and document hangs some time.

obj.ServerSideExport( vRootFileLocation + vQVDs + 'Export_' + vExportTime + '.qvd' , 4) ;

Not sure why.

Re: VBScript to JScript - Help in converting!

dmohanty — Tue, 05 Apr 2016 22:00:45 GMT

Hey petter-s‌ , petter.skjolden‌,

Good News!

I used like this instead of the vExportTime and it worked magically.

obj.ServerSideExport( vRootFileLocation + vQVDs + 'Export_' + vDocName + '_' + yr + m + d + hh + mm + ss + '.qvd' , 4) ;

Re: VBScript to JScript - Help in converting!

petter — Tue, 05 Apr 2016 22:07:40 GMT

So you can mark the question as answered then...

Re: VBScript to JScript - Help in converting!

dmohanty — Wed, 06 Apr 2016 02:08:00 GMT

Hi petter-s‌, petter.skjolden‌

Surely I am going to mark it now as correct for your great help.

However, as small twist - The List Box is exporting all the records from it. I was expecting only the Possible/Selected Values only.

Any other script we need to add?

I am trying for some other changes to make it possible.

Re: VBScript to JScript - Help in converting!

Wed, 06 Apr 2016 03:00:28 GMT

Instead of LB, create a Table Box with single filed and minimize the TB object. Try to export Table Box to QVD.

I just wondering what is use case storing LB to QVD?

Re: VBScript to JScript - Help in converting!

tamilarasu — Wed, 06 Apr 2016 04:57:27 GMT

You can also try,

function Test()

{

currtime = new Date();

vYear = currtime.getFullYear();

vMonth = ('0' + (currtime.getMonth()+1)).slice(-2);

vDay = ('0' + (currtime.getDate())).slice(-2);

vHour = ('0' + (currtime.getHours())).slice(-2);

vMinute = ('0' + (currtime.getMinutes())).slice(-2);

vSeconds = ('0' + (currtime.getSeconds())).slice(-2);

vExportTime = vYear + vMonth + vDay + " " + vHour + vMinute + vSeconds;

doc = ActiveDocument;

vRootFileLocation = doc.GetVariable('vRootFileLocation').GetContent().String;

vQVDs = doc.GetVariable('vQVDs').GetContent().String;

vDocName = doc.GetVariable('vDocName').GetContent().String;

obj = doc.GetSheetObject("LB_Bookmark");

obj.ServerSideExport( vRootFileLocation + vQVDs + "Export_" + vDocName + vExportTime + ".qvd",4);

};