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]

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

[dana]

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

[marcus_sommer]

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

- Marcus

[dana]

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...

[marcus_sommer · Accepted solution]

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

[dana]

Thank a lot, Marcus!

I Will proceed to check what you've recommended.

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

[dana]

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/124...

Thanks for your kind help.!

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

[marcus_sommer]

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

[dana]

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!