Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

Community Browser