topic Re: Call a function with parms in script in QlikView

Call a function with parms in script

Joaquin_Lazaro — Fri, 28 Sep 2018 08:33:32 GMT

Hello:

I have this function and it works on Desktop with Ctrl+M and Test button.

function open_and_save

vFile = "C:\Temp\Informe Conversiones.xlsx"

set xlApp = CreateObject("Excel.Application")

xlApp.Visible = false

xlApp.ScreenUpdating = false

xlApp.DisplayAlerts = false

xlApp.Workbooks.Open vFile

Set xlDoc = xlApp.Workbooks(1)

xlDoc.Save

xlDoc.Close

xlApp.Quit

set xlDoc = nothing

set xlApp = nothing

end function

I need to add a parm vFile and call it from script but I'm not able to do, any tip or help or suggest ?

This is what I need to do in the Module Editor

function open_and_save (vFile)

and a call or any other sentece in Script

Of course any combination of these sentences have worked

vFile = 'C:\Temp\Informe Conversiones.xlsx';

vFile = chr(34) & 'C:\Temp\Informe Conversiones.xlsx' & chr(34);

LET vRes = open_and_save($(vFile));

LET vRes = open_and_save(vFile);

Trace vRes= $(vRes);

Please help me

Thanks in advance

Joaquín

Re: Call a function with parms in script

marcus_sommer — Fri, 28 Sep 2018 12:10:26 GMT

Not each kind of macro will work by calling it from the script, for example everything which needs a ActiveDocument - by an ActiveX statement like createobject() I believe it will but I'm not sure.

Further your code missed a return-value of the function like:

open_and_save = TRUE

Also the parm-creation needs a bit adjusted to:

SET vFile = "C:\Temp\Informe Conversiones.xlsx";

Beside this was is the reason for opening an Excel during the script-run?

- Marcus

Re: Call a function with parms in script

Joaquin_Lazaro — Fri, 28 Sep 2018 13:57:49 GMT

Hi Marcus:

Thanks for helping me

I'm not using any ActiveDocument object neither property, do I ?

I have added your instructions and I still have the issue

I need to open and save an excel until Qlik solve and publish the bug QV-15143

What I'm doing wrong ?

Re: Call a function with parms in script

Miguel_Angel_Baeyens — Fri, 28 Sep 2018 14:15:19 GMT

If it's a bug, shouldn't you wait until it's fixed?

As a workaround in the meantime, you can use that code in an external VBS file and call it from the script using EXEC.

I wouldn't be surprised if the account running the QlikView services lacks the right to create that file in that folder, while your account does have it.

I guess Excel is installed in that computer and the macro does not generate any interactive dialog.

Re: Call a function with parms in script

marcus_sommer — Fri, 28 Sep 2018 14:17:25 GMT

What do you mean with: "I need to open and save an excel until Qlik solve and publish the bug QV-15143" ? What is the aim of opening, saving and closing an Excel while a script runs? You could not write or export anything from the script into the Excel per macro - is this the aim?

- Marcus

Re: Call a function with parms in script

Joaquin_Lazaro — Fri, 28 Sep 2018 14:23:18 GMT

Marcus:

If I read the excel with a load, the script load blanks or nulls; if I open and save manually the excel and then I run the script it works fine. This is the reason

The macro code runs well now, using

SET vFile = "C:\Temp\Informe Conversiones.xlsx";

as you said before.

I had some MSGBOX to debug the code; I removed them and every works fine.

Thanks for your help. again

Re: Call a function with parms in script

Joaquin_Lazaro — Fri, 28 Sep 2018 14:28:18 GMT

Hi Miguel:

By the way, this is a workaround until the bug is fixed. There are a lot of different workarounds

If the bug were fixed tomorrow, of course I wait ... but there are not a date

As I said in my previous post, finally it works fine

Thanks for your help, too

Re: Call a function with parms in script

Miguel_Angel_Baeyens — Fri, 28 Sep 2018 14:44:32 GMT

One of the issues with macros in the script is that the account running the QlikView Services often (and should) lack of interactive rights. These accounts run in Session 0 which means that, whatever prompt, dialog or window which may pop up as a result of the execution of the macro will stop the script, as the account never "sees" those items popping up.

So if there is a window asking for "Save as" or a warning with an "OK" button, these will never be "clicked". It works for you because you can interact with those dialogs and click.

The existence of those MSGBOXes seems to explain all the above.

Re: Call a function with parms in script

marcus_sommer — Fri, 28 Sep 2018 15:02:43 GMT

I think your problem is not caused from Qlik else from the Excel respectively the tool which has created this file. There were already similar cases here in the community and if I remember correctly these files were mainly created from an online Excel and/or stored on Sharepoint or something similar and they have a slight different file-structure to the default file-structure which a "normal" file would create.

While Qlik and probably some further tools will fail to open these files (correctly) is Excel more robust and "repairs" the file-structure during another save-statement. If this is true than it's rather unlikely that there will be a fix from the Qlik side and I wouldn't be also very optimistic that there will be soon a change on the Microsoft side.

I'm glad that it now worked for you whereby I think I would rather use an external batch triggered with an EXECUTE statement than a macro-routine within the script. Here an example how it could be done: Re: Load excel files from web.

Beside this you could try to load this file per odbc: Loading Multiple Excel Sheets Dynamically along with file name and sheet name. Maybe the "error" is there not relevant.

- Marcus