Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
richnorris
Contributor II

Opening Excel file works locally but not from QMC?

Hi, I have a VBS script that opens an Excel document which is password protected, and then saves it somewhere else.

I then have a QVW which runs this via an EXECUTE command as part of the script.

If I run this QVW locally, everything works fine.

If I run this from QMC, it runs the VBS and reaches the part where it would open the Excel file, but just hangs. It does actually open Excel,

because I can see an Excel process running in task manager, but it wont continue beyond that?

The line it gets stuck on looks like:

Set wb = xl.Workbooks.Open("\\FileLocation", False, True, , "FilePassword")

Its not a permissions issue (its the same account locally as it is running QMC)

I don't want to script it out to an external task in publisher. (Because there is a whole bunch of entwined logic that I don't want to untangle for various reasons).

Does anyone know any reasons why this could be occurring? I've got enable unsafe macros turned on in the QMC security tab, but again, the VBS is definitely starting, its just getting 'stuck' with actually opening the Excel doc?

7 Replies
Highlighted
Not applicable

Re: Opening Excel file works locally but not from QMC?

Have you used a mapped drive in the path to your file or a full path?

MVP & Luminary
MVP & Luminary

Re: Opening Excel file works locally but not from QMC?

What do you get from this?

on error resume next

Set wb = xl.Workbooks.Open("\\FileLocation", False, True, , "FilePassword")

msgbox err.number & " - " & err.description

- Marcus

richnorris
Contributor II

Re: Opening Excel file works locally but not from QMC?

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.

richnorris
Contributor II

Re: Opening Excel file works locally but not from QMC?

ALSO! Thankyou for the suggestion!

MVP & Luminary
MVP & Luminary

Re: Opening Excel file works locally but not from QMC?

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:

Re: Module Code Argument Errors

Re: Restart the QDS_service on a regular basis?

- Marcus

richnorris
Contributor II

Re: Opening Excel file works locally but not from QMC?

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.

richnorris
Contributor II

Re: Opening Excel file works locally but not from QMC?

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.