topic Run Excel macro via QMC in QlikView

Run Excel macro via QMC

anil_kumar_bt — Tue, 24 May 2016 12:01:40 GMT

Hello all,

Could anyone please tell me "Is it possible to run macro in excel via QMC".

We have one excel and it contains several macros, right we have running the macro manually when open the excel file.

To automate this process we had thought like create qmc task to run the Macro.

If quick help will be much appreciated.

Re: Run Excel macro via QMC

marcus_sommer — Tue, 24 May 2016 13:12:08 GMT

You could use the execute-statement to trigger external programs, for example something like this:

Execute "C:\Program Files (x86)\Microsoft Office\OFFICE11\EXCEL.EXE" "D:\YourFile.xls"

You need to adjust the path to your current excel.exe. Noticeable is that these excel will be run like a background-service - it meant it won't be visible - maybe it could be changed by the excel-macro which needs to run with an OnOpen-trigger in this case. Therefore it could be helpful to include such things like a run-parameter.

Another possibility would be to use a dertour and start a windows tasks which then started your excel which I for example use if I need that the tasks runs with a different user:

EXECUTE schtasks.exe /Run /TN "QlikView Tasks\SynchronizeDataABC"

- Marcus

Re: Run Excel macro via QMC

Anonymous — Mon, 24 Oct 2016 09:59:27 GMT

Hi Marcus,

thank you for your answer.

I did a task "Start" in task scheduer but Execute command is not working:

EXECUTE "%windir%\system32\taskschd.msc /s";

I am running windows 2008 Server (task scheduer like in Vista).

Problem is from running vbs script from Publisher. Qlikview verion: 11.

All solutions in forum didn't fix my problem.

help for help,

Jacek Antek

Re: Run Excel macro via QMC

marcus_sommer — Mon, 24 Oct 2016 10:56:25 GMT

A statement like the following runs for me with Server 2008:

EXECUTE schtasks.exe /Run /TN "QlikView Tasks\SynchronizeDataABC"

Try it with manually triggering them in the qmc - if it then worked and not per qmc-schedule it meant that the cause is the login-user respectively a run without a user.

- Marcus

Re: Run Excel macro via QMC

Anonymous — Mon, 24 Oct 2016 12:36:55 GMT

Thank You Marcus.

For me:

EXECUTE schtasks.exe /Run /TN "Start"

doesnt work in QV application in script code.

In Task Scheduler i created task Start:

Task "Start" is running vbs script which is opening Excel with macro and closing it.

From Task Scheduer Task Start is working fine.

But from QV script it doesn't work at all.

Jacek

Re: Run Excel macro via QMC

marcus_sommer — Mon, 24 Oct 2016 18:08:33 GMT

How had you tried it - reloading the qvw within the desktop client or manually triggering the task within the qmc or per scheduled qmc-task. If not yet done - try it in the above mentioned order.

One reason could be that you hadn't enabled the execution of the EXECUTION statement. There is a setting within the user-properties within the tab security and also within the settings.ini from the server (it's different by older and youger releases): Authorizing the Script EXECUTE Statement | Qlikview Cookbook.

- Marcus

Re: Run Excel macro via QMC

Anonymous — Tue, 25 Oct 2016 06:04:31 GMT

Yes Marcus,

all matters of security things from post you have been provided I did and is still don't work.

I am triggering application manually from QV script editor and it is not working.

If it is not working from QMC if it is not working within Script loading...

I am trying to set up task scheduler without QlikView at all.

Jacek Antek

Re: Run Excel macro via QMC

marcus_sommer — Tue, 25 Oct 2016 06:58:02 GMT

If you are running this from the desktop client and it did't work it meant that there are certain settings on the windows-task which prevent the external execution from the task - or meant it didn't work the task will be executed but the export failed?

- Marcus

Re: Run Excel macro via QMC

Tue, 25 Oct 2016 07:52:43 GMT

Thank You Marcos for your support and help!

Maybe it was something wrong with my task scheduler - now it is working like a charm!

Warm regards,

Jacek Antek

Re: Run Excel macro via QMC

mingsum214 — Mon, 28 Aug 2023 10:35:30 GMT

I facing similar issue, which also calling a xlsm in QVW execute script. The testing is fine on QV desktop but when move to QMC reload, the qvw finish reload but the xlsm is not actuall running.

Any workaround or solution?

Re: Run Excel macro via QMC

marcus_sommer — Mon, 28 Aug 2023 12:27:17 GMT

The QMC user may not have the proper access rights and/or licences/configurations to perform the task. To exclude some of the potential issues do it again with the desktop client on the server-machine with the services users.

Re: Run Excel macro via QMC

mingsum214 — Tue, 29 Aug 2023 01:47:26 GMT

Tried the service user approach, same scenario - QV desktop work and QV server reload, for QV server we found EXCEL.exe stuck in process and unable to close.

Re: Run Excel macro via QMC

marcus_sommer — Tue, 29 Aug 2023 06:01:03 GMT

Very likely is there any popup within the Excel asking to login or for configurations like the default file-format or any other stuff which gets no respond and/or the execution of macros is restricted and/or linked content couldn't be resolved or ... a lot of possibilities. One way to exclude many of them would be to perform the entire Excel stuff manually - again on the server machine and with services user. Further within the Excel macro you should set the visibility and the responds of any kind of errors/messages to true to see what happens and where it may break.