topic Re: Issue running macro in template through task scheduler in QlikView

Issue running macro in template through task scheduler

canoebi99 — Thu, 05 Dec 2013 01:45:34 GMT

Hi all,

I have a macro in a template that basically loops through a field on a chart and then copies the chart to an excel file and saves the file down for each loop.

The macro works fine when I run it manually from a button. It also works fine if I set it to run through a bat file and double click on the bat file.

However, when I get task scheduler to run the bat file the process fails.

I am guessing it might be something to do with the part where it copies the chart to excel but I have no idea what might be stopping it. It is probably stopping at the set xlapp part? The same user can run a similar process fine through task scheduler but only writing out a csv file not copying a chart to excel like this process.

The bat file definitely starts running and then QV.exe just seems to sit there as if something in the macro is being blocked from happening.

The macro code is below:

SUB VBBxlsExport

DIM varDate

DIM d

DIM xlApp

DIM xlBook

DIM xlSheet

DIM strSheetName

DIM FieldName

DIM ff

DIM v

DIM varAutomation

SET v = ActiveDocument.Variables("vTriggerMacro")

varAutomation = v.GetContent.STRING

'SET d = ActiveDocument.Variables("vDate")

'varDate = d.GetContent.STRING

SET Doc = ActiveDocument

Doc.FIelds("DartRepairs.VolumeBuilder").SELECT "Yes"

SET ff = ActiveDocument.Variables("vFieldName2")

FieldName = ff.GetContent.STRING

SET Field = Doc.Fields(FieldName).GetPossibleValues

FOR i=0 to Field.Count-1

strFilename = Field.Item(i).Text

SET xlApp = CREATEOBJECT("Excel.Application")

xlApp.Visible = FALSE

SET xlBook = xlApp.Workbooks.Add

SET xlSheet = xlBook.Worksheets("Sheet1")

XLSFile = "c:\temp\"&strFilename&".xls"

Doc.Fields(FieldName).Clear

Doc.FIelds("DartRepairs.VolumeBuilder").SELECT "Yes"

Doc.FIelds(FieldName).SELECT Field.Item(i).Text

Doc.GetApplication.WaitForIdle

Doc.GetSheetObject("CH25").CopyTableToClipBoard TRUE

xlApp.ActiveSheet.Paste

xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireColumn.AutoFit

xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireRow.AutoFit

'strSheetName = Field.Item(i).Text

'xlApp.ActiveSheet.Name = strSheetName

xlApp.DisplayAlerts = FALSE

xlBook.SaveAs XLSFile, 56

xlBook.Close

XLSFile = ""

'end if

Doc.Fields(FieldName).Clear

Doc.FIelds("DartRepairs.VolumeBuilder").Clear

'msgbox "got to end of xl export"

END SUB

Does anyone have any ideas? I am really stuck with this one!

Many thanks

Ben

Re: Issue running macro in template through task scheduler

georges_galvan — Wed, 28 May 2014 10:29:23 GMT

Hi,

I wondering if you have found a solution to your post because I have a similar problem with a task scheduler and a similar macro.

To analyse this I create a subroutine to log each step of the macro execution.

I finally traced the problem in the instruction xlBook.SaveAs.

I working right now to fix this...

Any update from your side?

Regards

Georges

Re: Issue running macro in template through task scheduler

marcus_sommer — Wed, 28 May 2014 10:48:09 GMT

Has the user-account on which the task runs appropriate access-rights on the path? I assume if you run this script manually it will be work?

- Marcus

Re: Issue running macro in template through task scheduler

georges_galvan — Wed, 28 May 2014 13:35:31 GMT

Hi,

Yes, user account has access to the path.

In fact, like in the post from Ben Robinson, the task scheduler launch a .bat and in this case the Macro stops in the SaveAs command (in the meantime, the macro has create and update the log file in the same directory).

When this .bat is launched manually, everything works.

I created another task in the task scheduler with my user account, and this task -launched with the user account having the problem- works fine !

Therefore, it seems something related to an authorisation/rights issue that concerns the saveAs of the Excel application object.

Any suggestion?

Regards

Georges

Re: Issue running macro in template through task scheduler

marcus_sommer — Wed, 28 May 2014 14:14:13 GMT

Probably there are two reasons. First: runs a task with a certain user-account it won't be have the same access/security rights how the user has if he did it manually. I'm sure these settings could be changed but I don't know how. Second: such task run as a kind of background-services and sometimes the programs needed more visibility or access on any *.dll.

Therefore I use a mix-up from task with my user and admin-user and visibilty for the applications. You could try to change xlApp.Visible = FALSE into TRUE.

Maybe it could be an alternative if you don't create a new file else you take a copy from an existent file or used the built-in vbs-functionalities to exporting objects from qv and make your changes afterwards.

- Marcus

Re: Issue running macro in template through task scheduler

canoebi99 — Fri, 30 May 2014 02:04:51 GMT

Hi Georges,

Apologies for the slow response. I never did find an answer to this and I actually ended up recoding it and pushing it out as a csv file without any formatting. I think my issues where to do with the fact that the method I was using can't be run as a background process which is what happens when running a scheduled task.

Interestingly enough though I have come across a similar issue with a completely different piece of software that is converting a pdf to excel and I have debugged this down to the point of also failing at the save of the excel file (it all works perfectly fine when run manually). I still haven't resolved this one! I am sure it will be related to rights and the fact that when running as a scheduled task (and hence background process) things aren't quite the same as running as the actual user.

If I ever do get an answer to this I will post back.

Regards

Ben

Re: Issue running macro in template through task scheduler

canoebi99 — Fri, 30 May 2014 02:17:52 GMT

Hi again Georges,

I was just researching this and may have stumbled across a possible solution ... :

I haven't had chance to give it a go so don't know if it will work but I will try it when I get onsite..... if you get chance to try it first let me know how you get on and if it makes any difference!

You have to create a folder (or two folders on a 64bit-windows):

(32Bit, always)

C:\Windows\System32\config\systemprofile\Dektop

(64Bit create this as well as the above)

C:\Windows\SysWOW64\config\systemprofile\Desktop

That's it ... once you have created those folders retry the scheduled task.

I'll post back when I get a chance to give it a go.

Cheers

Ben

Re: Issue running macro in template through task scheduler

georges_galvan — Fri, 30 May 2014 10:00:25 GMT

Hi Ben,

You got it... your fix works OK.!!

Thanks you very much!

Just for the fun to understand, have you got an explanation?

So, it looks something related to the Excel behaviour in the background...

Cheers

Georges

Re: Issue running macro in template through task scheduler

canoebi99 — Mon, 02 Jun 2014 21:30:08 GMT

Hi Georges, sorry for the slow response.

Glad that worked for you! I will get out and try it on my site this week and keep my fingers crossed!

I'm sorry but I can't give you much on the workings of this. It is certainly excel related and it was your post that made me think it might be (i.e. I wasn't the only person with the issue).

I found the info I gave you on a microsoft forum and thought it was worth a try (link below)!

Run Powershell script as Scheduled task, that uses Excel COM object

Regards

Ben

Re: Issue running macro in template through task scheduler

georges_galvan — Wed, 04 Jun 2014 15:48:33 GMT

Hi Bob,

Thanks for the information.

Hope it will wok for you also.

(Actually it looks like a magic trick! )

Cheers

Georges