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:
SET v = ActiveDocument.Variables("vTriggerMacro")
varAutomation = v.GetContent.STRING
'SET d = ActiveDocument.Variables("vDate")
'varDate = d.GetContent.STRING
SET Doc = ActiveDocument
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"
'strSheetName = Field.Item(i).Text
'xlApp.ActiveSheet.Name = strSheetName
xlApp.DisplayAlerts = FALSE
xlBook.SaveAs XLSFile, 56
XLSFile = ""
'msgbox "got to end of xl export"
Does anyone have any ideas? I am really stuck with this one!
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?
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.
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.
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.
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):
(64Bit create this as well as the above)
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.
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...
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)!