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
