7 Replies Latest reply: May 20, 2016 11:28 AM by Richard Norris RSS

    Opening Excel file works locally but not from QMC?

    Richard Norris

      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?

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

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

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

              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.

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

              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

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

                  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.

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

                  ALSO! Thankyou for the suggestion!

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

                    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.