Macro stopped working in QMC

[dana]

Hi People,

I have a model with EXECUTE of a BAT that runs a VBS file.

It worked fine in the QMC.

Now it stopped working in the QMC, but still can be executed manually.

The problem seems to be with the following VBS command:

myXL.WorkBooks.OpenText "F:\QlikView\EXCEL\MyFile.csv", , , xlDelimited, , , , , True

Any idea what could cause the issue?

Thanks in advance!

[marcus_sommer]

Could you watch the execution? Some included msgbox could be helpful to monitor it step by step.

Another trial could go to ignore the error with:

on error resume next 

and then

msgbox err.number & chr(10) & err.description

- Marcus

[dana]

Hi Marcus,

When run manually - it displays the message  box with  0 as error code.

When run from the Task Scheduler - the task keeps running.

It doesn't show the message box.

As before, I  had to end it manually.

Thanks

[marcus_sommer]

Ok. No error is returned. If a path is wrong or no proper access-rights exists or similar stuff happens an error would be returned. But if any security-setting prevents the access I'm not sure if there would be always such error ...

Things which you may further trying is to include some sleep-statements and ensuring that there is no file-lock from the csv-creating process before the opening-statement is performed. An alternatively to the direct open of the file might be to add a file-dialog to browse to the target - maybe it could give more insights ...

Beside the above such no error could occur if any popup is shown and waiting for any user inter-action. Therefore, my suggestion to watch the execution if possible step by step. If it's not possible you may try a very ugly thing by emulating the keyboard and pressing esc or maybe (the numerical index of) "ok." ...

Further helpful would be to compare the environments - how does the Office installation and configuration differentiate each other.

- Marcus

[dana]

Hi Marcus,

I have tried some of the above:

deleting files in the BAT, and then running - but it didn't resolve the issue.

I couldn't find a way to emulate an input.. 

Please note that the Task Scheduler runs fine when defined with the first option, but not with the second,

even when I'm logged in as a QVADMIN

In both cases, the user is System:

I found out that the  "local administrator password solution" (LAPS) was installed. Seems like it caused the issue..but I still have to figure out how..

Thanks!

[marcus_sommer]

This seems to be ok. unless we use it with the second option if a user is logged or not - in respect with our services-user and with the highest privilegs. That within the task-details the task runs with the user system is IMO also ok. because each process must have an user even if none is directly logged. The important setting is in which context of access rights the task is executed and this your set services-user.

In regard to "local administrator password solution" (LAPS) I don't know if we have installed it too and if it's related to the matter. But I do remember that we had within a previous environment various task - not only QlikView related ones - which didn't always run properly and we used often an extra start-task which executed the final task with a RUNAS statement. Within the newer environment this didn't work anymore and we found other ways for the remaining tasks (most tasks were outdated).

Beside this what happens if you finish the task after the csv-creation and doing then the opening + saving part within a following task and/or manually? Further attempts could go to not opening this csv else creating a new workbook with an empty worksheet and saving it - if this didn't work it hints for causes which are not related to the csv-file. But if it worked you may use this as starting point for a workaround by not writing your content within a csv else writing it directly in Excel.

- Marcus

[dana]

Hi Marcus,

I have updated the vbs to read a new Excel file, and used WorkBooks.Open unstead of myXL.WorkBooks.OpenText.

The behavior is the same: works manually, but not automatically..

I found an article with a similar issue:

https://stackoverflow.com/questions/74052821/task-scheduler-failed-to-run-the-excel-vba-macro

The IT people are still trying to figure out the permissions..

So I guess I'll wait and see if they can resolve the issue on their side.

Thanks so much for your guidance!

[dana]

Hi Marcus,

I finally found the solution in this thread:

https://superuser.com/questions/579900/why-cant-excel-open-a-file-when-run-from-task-scheduler

Create these two folders:

32Bit:

C:\Windows\System32\config\systemprofile\Desktop  

64Bit:

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

Excel needs these folders if it's not run interactively. Create both folders even if you are on a 64-bit OS.

It resolved the issue in the QMC as well..

It's still a mystery why it worked so far without the folders..

Thanks for your help!