Issue running macro in template through task scheduler

[canoebi99]

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

NEXT

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

[georges_galvan]

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

[marcus_sommer]

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

[georges_galvan]

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

[marcus_sommer]

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

[canoebi99]

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

[canoebi99]

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

[georges_galvan]

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

[canoebi99]

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

[georges_galvan]

Hi Bob,

Thanks for the information.

Hope it will wok for you also.

(Actually it looks like a magic trick! )

Cheers

Georges