Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

chesterluck
Not applicable

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
Not applicable

Re: Macro not working when executed through task

16 Replies
marcus_sommer
Not applicable

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
Not applicable

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

marcus_sommer
Not applicable

Re: Macro not working when executed through task

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

chesterluck
Not applicable

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    

jerrysvensson
Not applicable

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
Not applicable

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

jerrysvensson
Not applicable

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
Not applicable

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.

jerrysvensson
Not applicable

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.