Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
chesterluck
Contributor II

Macro not working when executed through task

Hello community,

I'm standing infront of a quite special problem. I have a file which is starting a skript triggered by opening this file.

The file is opened by a task which is opening a batch.

The macro itself starts first a batch and the it is doing some excel operations:

'BAT operation

    MoveXLS = "D:\tasks\some.bat"

    Set ShellApp = CreateObject("Shell.Application")

                ShellApp.Open (MoveXLS)

                ActiveDocument.getApplication.sleep 20000

                Set ShellApp = Nothing

   

'Excel operation

XLSimport = "\\server\import.xlsx"

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

Set objXLS = AppExcel.Workbooks.Open(XLSimport)

....

When the file was opened through the task then the Macro is only doing the batch operation.

When im starting the batch manually then everything is working.

I don't know what is the reason for this issue.

Other triggers are working fine, for instance when I want to save a table in an xle format.

Until now it is the first time im using vb to CreateObject("Excel.Application").

I suppose somewhere here the root of the problem must be...

Thank you in advance

//chesterluck

Tags (3)
1 Solution

Accepted Solutions
chesterluck
Contributor II

Re: Macro not working when executed through task

16 Replies
MVP & Luminary
MVP & Luminary

Re: Macro not working when executed through task

With which user runs the task - perhaps it failed because of not proper access rights.

- Marcus

chesterluck
Contributor II

Re: Macro not working when executed through task

Well the other tasks (working on the same folder) work just fine. So I can exclude this reason..

Edited: I checked the user anyway. Its the same user with which I opened the .bat manually..

MVP & Luminary
MVP & Luminary

Re: Macro not working when executed through task

For me it worked - are this one or several trigger/routine?

chesterluck
Contributor II

Re: Macro not working when executed through task

I don't understand how you mean it.

Well the qvw is started throught task (which starts a batch), in the qvw there is an on open trigger which is starting some code. The code snippets i showed in the orig thread. There are some more code interacting with the excel app.

I've found some more interesting information, but unfortunately this also didnt work for me:

http://community.qlik.com/message/169755#169755

I will try to create some error handling and write the err.msg inside of a variable in the qvw. The results Ill post in a min

thx

chesterluck    

Partner
Partner

Re: Macro not working when executed through task

Have you tried to put your Excel code in a vbs script and run it?

You can use : cscript  exceltest.vbs, your code will be in exceltest.vbs

chesterluck
Contributor II

Re: Macro not working when executed through task

I still didn't implement the error handling. But correct me if I understood you wrong:

- I do not have any code executed through a makto in excel.

- The code is only in the qvw

- It is executed fine when Im signed in, but not if a task is triggering the batch file...

cheers chesterluck

Partner
Partner

Re: Macro not working when executed through task

I was talking about this code :

XLSimport = "\\server\import.xlsx"

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

Set objXLS = AppExcel.Workbooks.Open(XLSimport)

..

Put it in a vbs file and run it with cscript.exe.

If that doesn't work when you are not signed in, it will not work in QlikView either.

chesterluck
Contributor II

Re: Macro not working when executed through task

Well but the question is why - I do know that it works when Im signed in and doesn't work when not.

Partner
Partner

Re: Macro not working when executed through task

Probably because Excel needs a window. When you run the job without being logged on it runs in SYSTEM account that doesn't have a window.

This works, creates a file. No need for a window:

strPath = "\\vbscript\_test.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.CreateTextFile(strPath)

objFile.Write "hello"

objFile.Close

set objFile = nothing

This doesn't work. Needs a window:

Set MyApp = CreateObject("QlikTech.QlikView") 

Set MyDoc = MyApp.OpenDoc ("\\vbscript\test.qvw","","")

Some people have gotten Excel to work by creating the Desktop catalog, but not everyone.