topic Re: Macro not working when executed through task in QlikView

Macro not working when executed through task

Anonymous — Mon, 04 Nov 2013 10:02:20 GMT

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

Re: Macro not working when executed through task

marcus_sommer — Mon, 04 Nov 2013 12:07:02 GMT

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

- Marcus

Re: Macro not working when executed through task

Anonymous — Mon, 04 Nov 2013 12:09:14 GMT

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

Re: Macro not working when executed through task

marcus_sommer — Mon, 04 Nov 2013 14:35:56 GMT

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

Re: Macro not working when executed through task

Anonymous — Mon, 04 Nov 2013 14:53:30 GMT

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

Re: Macro not working when executed through task

jerrysvensson — Mon, 04 Nov 2013 15:39:36 GMT

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

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 12:32:35 GMT

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

Re: Macro not working when executed through task

jerrysvensson — Tue, 05 Nov 2013 12:45:01 GMT

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.

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 12:53:10 GMT

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

Re: Macro not working when executed through task

jerrysvensson — Tue, 05 Nov 2013 13:19:41 GMT

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.

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 13:22:32 GMT

Thats the err.num & desc:

462 - remote server machine not found

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 13:27:21 GMT

I'll check it - sounds plausible.

Maybe this raw somehow creates a problem: AppExcel.Visible = true???

Re: Macro not working when executed through task

jerrysvensson — Tue, 05 Nov 2013 13:39:39 GMT

Maybe, try to remove it. 🙂

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 13:59:41 GMT

XLSimport = "\\server\import.xlsx"

Set AppExcel = CreateObject("Excel.Application")
AppExcel.Visible = False
Set objXLS = AppExcel.Workbooks.Open(XLSimport)

'-----------------------------------------------------------------------------------------------------------------Parameter set

Set curSheet_St = AppExcel.WorkSheets(1)

'-----------------------------------------------------------------------------------------------------------------Clear

curSheet_St.Range("B4:I17").ClearContents

AppExcel.Sheets("Import_QV").Range("A1:F11").ClearContents
Set GraphNumToExport_St = ActiveDocument.GetSheetObject("CH49")
GraphNumToExport_St.CopyTableToClipboard true

curSheet_St.Paste curSheet_St.Range("B4")

So tell me once again - and forgive me for many stupid questions - how can I make this code work? You mean that this can't work: Set objXLS = AppExcel.Workbooks.Open(XLSimport)?

Thanks in advance

//chesterluck

Re: Macro not working when executed through task

jerrysvensson — Tue, 05 Nov 2013 14:24:48 GMT

If this Run macro from server

doesn't work, I can't help you. We are facing a similar problem..

Have you tried this?

Set up a scheduled task like this:

"c:\program files\qlikview\qv.exe" /r yourapp.qvw

Location of qv.exe might be different.

Re: Macro not working when executed through task

Anonymous — Tue, 05 Nov 2013 15:04:31 GMT

CD C:\Program Files\QlikView Desktop\

Qv.exe /r D:\QlikView\Documents\sth.qvw

Taskkill Qv.exe

EXIT

Well this im doing already. The error from error handling is this

462 - Remote server machine not found

Re: Macro not working when executed through task

Anonymous — Wed, 06 Nov 2013 12:54:35 GMT

http://social.msdn.microsoft.com/Forums/en-US/b81a3c4e-62db-488b-af06-44421818ef91/excel-2007-automation-on-top-of-a-windows-server-2008-x64?forum=innovateonoffice

DO NOT FORGET TO RESTART WINDOWS