reload password protected excel via QMC

Fri, 13 Jun 2014 16:20:25 GMT

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

topic reload password protected excel via QMC in QlikView

reload password protected excel via QMC