Thanks Helen, tried with both. Again though, bear in mind this isn't an issue with the script, the script works fine locally, nor is it (we have now determined) an issue with permissions, as we know for sure that the Service account that the QMC runs under has full access to perform the task. Interestingly, the entire process works on an old server, with an earlier version of Windows Server, Qlikview Server and .NET.
This leads me to believe it is most likely some configuration of QVS / Publisher that is the issue.
Well, bear in mind I'm running this from the QMC, where would you expect to see a popup? I'm running it and I can see a background process of a notification, but there's no way to see what that is. If I run it locally, I get a popup with just a 0- (I guess thats no error code, plus the -, plus the lack of description for no error?)
Is there a way to instead of having a messagebox, write the error to a file? Then I can see it outside of the background process. The other issue with this is, I don't actually get the impression there's an 'error', (otherwise I would expect the VBS script to fail) I suspect more likely its just that trying to open the file but this is generating a dialogue box ("Do you want to replace an existing file Y / N" type thing, although not that exact situation) but that there's no way to press yes / continue?
So frustrating, I wish I could just see what was happening.
Yes it could be that there is some nasty dialog, maybe you could see it with:
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
Another thought is to prevent such dialogs with:
XLApp.DisplayAlerts = False
But you could also create a log-file of your routine. Here are two examples how it could be done:
For anyone following / finding this thread in their search for a similar issue:
In the end, we had a somewhat defeatist solution to this. The problem seems to be with QMC connecting to a network share and opening an encrypted / passworded Excel document. This was formerly not a problem, but, it seems that with newer versions of Office, it causes a problem.
To solve the issue, we first run a batch file via an external task that copies the file from the network share to a local drive. For some reason, this works fine. Then, once the file has been copied locally, the QVW task that runs the VBS script which opens and resaves the document without the password can run fine.
One issue that we did come across, that was particularly bizarre, was that where previously we had been using the command:
EXECUTE CMD.EXE /c *****
we now have to instead use
EXECUTE CMD /c ******
for it to work. I'm not entirely sure how something like that is possible, but there we are.