topic Re: Run Excel from QMC in QlikView

Run Excel from QMC

lylererger — Tue, 06 Oct 2015 13:28:52 GMT

Hi friends!

Please help me to understand, why i can't run *.XLSM file with trigger ON_OPEN() to store TXT file into some folder from QlikView Management Console?

I can do that locally from QV Desktop.

To run Excel document, i'm using "EXECUTE cmd.exe /C start" command.

If i'm using path like that: "\\x00-0000-Qlik\С:\Project\and so on..." it's doesn't help...

Please help me to solve that problem !!!

Thanks in advance!

P.S.: Sources in attachment.

Re: Run Excel from QMC

petter — Tue, 06 Oct 2015 13:48:55 GMT

It is technical possible to run Excel via QMC and you don't need to use a load script or a QVW to execute Excel.exe with parameters in batch. You can add a SYSTEM / SUPPORTING TASKS / EXTERNAL PROGRAM:

The command line can be c:\Program Files\Office ...... \ EXCEL.EXE <some-parameters>

You have to test thoroughly and beware that a desktop program might not be extremely robust while running in batch. But it will work as long as you give the account that will execute the program the right permissions etc.

Even though it is technically possible it might not be advisable in a lot of production environements - that is up to you to decide if it is according to your company's policy....

Re: Run Excel from QMC

petter — Tue, 06 Oct 2015 13:50:46 GMT

Re: Run Excel from QMC

Peter_Cammaert — Tue, 06 Oct 2015 13:54:11 GMT

Didn't Microsoft block the execution of Excel without a logged-in user in recent versions of MS-Office?

Peter

Re: Run Excel from QMC

petter — Tue, 06 Oct 2015 14:07:30 GMT

Well when I successfully scheduled Excel 2013 in batch I had to manually create a speccial sub directory for the account that is running the batch. That constitutes a block....

Re: Run Excel from QMC

petter — Tue, 06 Oct 2015 14:15:10 GMT

Create these two folders:

32Bit:

C:\Windows\System32\config\systemprofile\Desktop

64Bit:

C:\Windows\SysWOW64\config\systemprofile\Desktop

Re: Run Excel from QMC

marcus_sommer — Tue, 06 Oct 2015 14:35:36 GMT

It's very interesting and worked - could you give some background to this? Is it only office which needs respectively had such "special" configurations - I have notice that various automatism from me needs a logged-in user and must be run visible but some of them seems to be a lot easier to solve (then my various workarounds).

- Marcus

Re: Run Excel from QMC

lylererger — Tue, 06 Oct 2015 14:40:10 GMT

Petter thank's. But what if i'm using QV Server, Not Publisher and I need to use my qvw?

Results of VBA calulations in .xlsm file i'm storing into QlikView.

Why that so hard to do that in QMC?

LET vtmp1File = 'DEMO.xlsm';

EXECUTE cmd.exe /C start $(vtmp1File);

I changed safety settings in excel for the user to run a services:

And still no any results.

Re: Run Excel from QMC

petter — Tue, 06 Oct 2015 14:41:02 GMT

Actually I haven't run into it with other software no...

It might be a measure to increase safety from Microsoft's side. So a user with administrative authority on the computer has to manually enable such use of Excel.

Re: Run Excel from QMC

marcus_sommer — Tue, 06 Oct 2015 14:57:58 GMT

You could try it with vbs instead of cmd - this one worked as example by me:

QlikView:

SET vCSCRIPT = 'c:\windows\system32\cscript.exe';

EXECUTE $(vCSCRIPT) "D:\ReadAndWriteExcelSheets.vbs";

VBS:

dim sourcepath, sourcefile, xls, ws, wsNames, fso, targetpath, targetfile

sourcepath = "D:\"

sourcefile = "TestExportPerAction.xls"

targetpath = "D:\"

targetfile = "SheetListing.txt"

set xls = createobject("Excel.Application")

xls.Workbooks.open sourcepath & sourcefile

wsNames = "WorkSheets" & chr(13) & chr(10)

for each ws in xls.worksheets

wsNames = wsNames & ws.Name & chr(13) & chr(10)

set fso = createobject("scripting.fileSystemobject")

set targetfile = fso.opentextfile(targetpath & targetfile, 2, true)

targetfile.write wsNames

targetfile.close

xls.Application.Quit

This has further advantages - no query to mcaro-execution and an OnOpen-Trigger should be work, too. And if not you could here also put other routines - and overall you have more flexibility with vbs.

- Marcus