Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 ?
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.
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
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
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
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.
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