Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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

0 Replies