topic Re: Macro stopped working in QMC in QlikView

Macro stopped working in QMC

dana — Wed, 23 Nov 2022 21:02:55 GMT

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!

Re: Macro stopped working in QMC

marcus_sommer — Thu, 24 Nov 2022 08:52:31 GMT

Executed manually means on the server-machine and with the user which runs the QlikView services? If not, check this out - and if there are any statements included which hide the execution or suppress any errors or messages comment them or change their true/false entry.

- Marcus

Re: Macro stopped working in QMC

dana — Thu, 24 Nov 2022 09:48:47 GMT

Hi Marcus,

The manual model load is with the same user as the QMC.

But the issue is not really relevant to QV...

The batch and the VBS run manually as expected.

When running with Windows Task Scheduler, the issue occurs.

Bellowis the VBS.

I added an alert before the problematic code, but it is not displayed.

' ============== UTF8 TO UNICODE ================

Const adTypeBinary = 1
Const adTypeText = 2
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2
Const adWriteLine = 1
Const adReadLine = -2
Const adLF = 10
Const adCR = 13
Const adCRLF = -1
Set streamUTF8 = CreateObject("ADODB.Stream")
Set streamUNICODE = CreateObject("ADODB.Stream")

streamUTF8.Open
streamUTF8.Type = adTypeText
streamUTF8.LineSeparator = adCRLF
streamUTF8.Charset = "UTF-8"
streamUTF8.LoadFromFile "F:\QlikView\EXCEL\MyFile.csv"

streamUNICODE.Open
streamUNICODE.Type = adTypeText
streamUNICODE.LineSeparator = adCRLF

Do Until streamUTF8.EOS
streamUNICODE.WriteText streamUTF8.ReadText(adReadLine), adWriteLine
Loop

streamUNICODE.SaveToFile "F:\QlikView\EXCEL\MyFile-U.CSV", adSaveCreateOverWrite
streamUNICODE.Close

' ============== CSV TO XLS ================

Dim myXL
Const xlDelimited = 1
Const xlWorkbookNormal = -4143

Set myXL=CreateObject("Excel.Application")
myXL.Visible=True
myXL.DisplayAlerts=True
myXL.WorkBooks.OpenText "F:\QlikView\EXCEL\MyFile-U.CSV", , , xlDelimited, , , , , True

myXL.DisplayAlerts=False
myXL.ActiveWorkbook.SaveAs "F:\QlikView\EXCEL\MyFile.xls", xlWorkbookNormal
myXL.DisplayAlerts=True

myXL.ActiveWorkbook.Close False
myXL.Quit

Set myXL = Nothing

Re: Macro stopped working in QMC

marcus_sommer — Thu, 24 Nov 2022 10:52:59 GMT

What happens if you manually start Excel and further then opening then the csv? There may any popup requiring any user-interaction ...

- Marcus

Re: Macro stopped working in QMC

dana — Thu, 24 Nov 2022 11:14:24 GMT

Nope...

Since the server was updated with some security component, this might caused a permission issue..

Still trying to figure what needs to be done...

Re: Macro stopped working in QMC

marcus_sommer — Thu, 24 Nov 2022 11:56:16 GMT

There were recently changes how office macros will be handled and also how the OS handled files from the internet or other insure sources.

For the first hint you could try to look within the Excel security settings. Disabling everything is of course not a good idea but the tool should give a message if anything violated the rules and is therefore restricted. To check against the second hint you could just remove the csv and replacing it with a new one - an empty one directly created on the machine.

But if I understand you right then worked the direct execution of bat + vbs but not if they are called from the windows task planner. In this case it becomes more difficult ...

Are you sure that the error happens exactly on the red line? If not try to find the point where it breaks. A rather simple way is to add some msgbox-statements returning just 1,2,3 ... or more helpful the values of some of the essential variables and/or loop-counter and if the breaking point is found also the err.number + err.description ... Quite helpful may also to add some sleep-statements to give the OS time to execute the I/O before the next statement wants to grab them again ...

- Marcus

Re: Macro stopped working in QMC

dana — Thu, 24 Nov 2022 12:06:37 GMT

Thank a lot, Marcus!

I Will proceed to check what you've recommended.

We also plan to contact Microsoft support to consult with them.

Re: Macro stopped working in QMC

dana — Tue, 29 Nov 2022 07:58:11 GMT

Hi Marcus,

The batch files worked properly when ran manually, but not by services.

the problematic line is:

myXL.WorkBooks.OpenText "F:\QlikView\EXCEL\MyFile-U.CSV", , , xlDelimited, , , , , True

As you suggested, I deleted and created the source CSV from Qlik:

streamUTF8.LoadFromFile "F:\QlikView\EXCEL\MyFile.csv"

This resolved the issue in the development server!

In the production server, where a security component was installed, it still didn't resolve the issue.

Bottom line:

As suggested in this post, the best way is to use NPrinting for the job...

https://community.qlik.com/t5/App-Development/Excel-CSV-Automation-of-a-Report-in-QlikSense/td-p/1244187

Thanks for your kind help.!

Would update this post when I resolve the issue on the production server.

Re: Macro stopped working in QMC

marcus_sommer — Tue, 29 Nov 2022 08:55:43 GMT

For just a few simple tasks NPrinting might be a bit overweight but if there are multiple and more or less often changing tasks NPrinting could save time and money because without such tool you are reinvent the wheel - especially in regard to get stable solutions which are simple to administer and documented.

Just some more hints to possible causes. Running a qmc-task or a windows-task means usually that no user is logged on the machine. This could mean that the task is completely or partly executed with a windows default user which may not always having the proper licences, configurations and/or access rights for the various task-parts. Further not all related tools/services may have all features available within such service-run without a user and a UI. Therefore, you may a bit play with it, for example by running this task if you were logged on the server and could monitor the execution - each disabling of messages, visibility, refresh the window and so on should be avoided. Maybe there occur any hint to the underlying cause ...

Before you may try some changes within the Office-settings - especially to enable the file-access for network-paths because I assume that your F: is a network-share. Otherwise you may try your task with all (new created) files on a local server-folder.

Here a starting-point for investigations to the security-setting-stuff:

https://learn.microsoft.com/de-de/DeployOffice/security/internet-macros-blocked

- Marcus

Re: Macro stopped working in QMC

dana — Tue, 29 Nov 2022 10:01:07 GMT

Hi Marcus,

Thanks for the valuable inputs!

I'm running the task scheduler when logged in with the same admin user of the task, but the issue persists.

I have created new files on local drive, but it didn't help.

BTW, on my laptop, everything works fine. I have office 365.

On the servers- the Office is installed.

Thanks!

Re: Macro stopped working in QMC

marcus_sommer — Tue, 29 Nov 2022 10:42:39 GMT

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

Re: Macro stopped working in QMC

dana — Tue, 29 Nov 2022 10:58:36 GMT

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

Re: Macro stopped working in QMC

marcus_sommer — Tue, 29 Nov 2022 12:56:52 GMT

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

Re: Macro stopped working in QMC

dana — Tue, 29 Nov 2022 18:24:26 GMT

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!

Re: Macro stopped working in QMC

marcus_sommer — Wed, 30 Nov 2022 09:42:40 GMT

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

Re: Macro stopped working in QMC

dana — Wed, 30 Nov 2022 16:48:53 GMT

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!

Re: Macro stopped working in QMC

dana — Wed, 07 Dec 2022 13:58:55 GMT

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!