Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
lylererger
Contributor II

Run Excel from QMC

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.

9 Replies
MVP
MVP

Re: Run Excel from QMC

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:

2015-10-06 #4.png

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....

MVP
MVP

Re: Run Excel from QMC

2015-10-06 #5.png

Re: Run Excel from QMC

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

Peter

MVP
MVP

Re: Run Excel from QMC

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....

MVP
MVP

Re: Run Excel from QMC

Create these two folders:

32Bit:

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

64Bit:

C:\Windows\SysWOW64\config\systemprofile\Desktop
MVP & Luminary
MVP & Luminary

Re: Run Excel from QMC

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

lylererger
Contributor II

Re: Run Excel from QMC

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:

1.png

And still no any results.

MVP
MVP

Re: Run Excel from QMC

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.

MVP & Luminary
MVP & Luminary

Re: Run Excel from QMC

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)

next

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