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

      set xl = nothing
      call CloseExcel

      End Function

       

      Then in the QV script I have:

      raport:
      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)
      load*;
      SQL SELECT *
      FROM `C:\Users\aaa\Desktop\test.xls`.`Arkusz1$`;

      DISCONNECT;

      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

       

      Regards

      Asia