0 Replies Latest reply: Jun 13, 2014 12:20 PM by Joanna Batycka RSS

    reload password protected excel via QMC

      Hi All,

      I have a problem to solve with data reload from password protected excel via the Qlikview Management Console.

      I create the macro which opens the file in excel and decrypt it, then there is the code that via the ODBC connects with the excel and reads the data. I took the steps as in the post here.


      Everything works perfectly when I trigger it within the Qlikview designer. When I try to start the task via QMC it gives the error:

      Error: SQL##f - SqlState: S1000, ErrorCode: 4294962291, ErrorMsg: [Microsoft][ODBC Excel Driver] Could not decrypt file.

      I have tried to log in to the server with Qlikview user name (the technical user)  - it is the same – it works fine when  reload manually. I have tried to use both the User DNS and the System DNS in the ODBC connection with no positive result.

      I am using Qlikview 11 SR6.


      My macro is:

      Function OpenExcel(filepath,pwd)

      set objExcel = CreateObject("Excel.application")
      set objWB = objExcel.Workbooks.Open(filepath,,,,pwd)

      end Function

      Function CloseExcel()

      on error resume next
      set xl = getobject(,"Excel.Application")
      xl.activeworkbook.saved = true

      set xl = nothing
      call CloseExcel

      End Function


      Then in the QV script I have:

      LOAD * INLINE [raport];

      set filepath='C:\Users\aaa\Desktop\test.xls';
      set pwd='haslo';

      let x = OpenExcel(filepath,pwd);
      ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\aaa\Desktop\test.xls];

      Concatenate (raport)
      SQL SELECT *
      FROM `C:\Users\aaa\Desktop\test.xls`.`Arkusz1$`;


      let x = CloseExcel();


      What am I doing wrong? Is there any additional things I have mark to make it work? In the QMC I have checked all the options which allow using macros.

      Thank you for any suggestions