Run Excel from QMC

[lylererger]

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.

[petter]

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

[petter]

[Peter_Cammaert]

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

Peter

[petter]

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

[petter]

Create these two folders:

32Bit:

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

64Bit:

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

[marcus_sommer]

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]

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.

[petter]

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.

[marcus_sommer]

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