Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
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

1 Solution

Accepted Solutions
16 Replies
marcus_sommer

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

- Marcus

Anonymous
Not applicable
Author

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

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

Anonymous
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

Anonymous
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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.

Anonymous
Not applicable
Author

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

jerrysvensson
Partner - Specialist II
Partner - Specialist II

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.