How to open Password protected Excel file in qlikview?

[siva_boggarapu]

Hello Everyone,

Good Morning!

I have one excel file which consist of Password. I am trying  to open  Password excel file in Qlikview, it's giving error like"Cannot read BIFF file".

Could you please explain me how to open?

Best,

Siva.

[Anonymous · Accepted solution]

Dear Siva,

Here is some work around.. but the excel file needs to be opened in  MS Excel first during the below steps..

1. The excel file should be opened first in the system where QlikView is extracting that excel data (with supplying the password)

2. Create ODBC connection for the encrypted (password protected) excel file. Provide DSN Name ,Choose the Excel file and proper excel version (latest is version12)

3. In the QlikView script, choose "ODBC" and connect,choose the DSN you created in step2 and provide password and leave the userid blank.

4.Test the connection

5.Click OK

6.Click "select" and "tick" the "system Fields" check box. You should be able to see the sheets.

NOTE

Whenever there is data extraction needed from the protected excel into QlikView, you should open the protected excel file in excel first otherwise you will get error. "SQL##f-SqlState:S1000,ErrorCode:4294962281,ErrorMsg:[Microsoft]ODBC Excel Driver]External table is not in the expected format".

SO KEEP the Excel file Open !!!!??? in the QlikView Server (where the data extraction happens )

Seems the the EXCEL ODBC driver needs the excel file to be opened in MS Excel..

If any other better way, pls update..

Let me know if it works...

Best,

Shubham

[siva_boggarapu]

Thank you shubham, It is working..but only the document should be open with password first or else it does give error.

best

Siva

[Anonymous]

Hello,

I create ODBC source and when I try to open XLS, QlikView is speeking: Could not decrypt file.

I have Win7 x64, but I use 32bit ODBC drivers. Office 2010, I tried XLS version 97-2003, version 12.0, I tried two others version of driver: 6.01.7600.16833 (ODBCJT32.DLL; 15.6.2011) and 14.00.6015.1000 (ACEODBC.DLL; 29.8.2011) - the same result.

Do you have any idea? Other way?

Thanks,

Olda

[bimala0507]

Hi:

I have excel 2013. I created the ODBC driver by the above mentioned method, but could not read it in QlikView. It does not show me the file for which I have created the ODBC. Is it because my Excel file is stored in .xlsx format and I am creating ODBC connection with .xls. What ODBC driver I should use to create DSN for Excel  2013 ?

Kind Regards,

Bimala

[Anonymous]

the simplest way to do it, we can directly fetch the option from 'Data from files' and use the Tables Files. Do you have any specific reason for creating the ODBC? what is the scenario?

[Zmirell]

Unfortunately Office 2007 and later uses 128bit AES encryption for passwords.  Microsoft did this since previous versions of Office were so easy to crack the passwords.  The only way to crack the password is to apply a brute force crack (that is try every possible word in the dictionary and beyond until the password is found). 

 i google and found many tools that can recover excel password:

https://www.iseepassword.com/open-a-password-protected-excel-file.html

https://www.wikihow.com/Open-a-Password-Protected-Excel-File

https://www.excel-easy.com/basics/protect.html

hope they're helpful! 

[SeanMoss]

There is nothing to say, if you want to open a password-protected Excel file in qlikview, you need to crack Excel password first, isn't it? Using the free Word/Excel Password Recovery Wizard, password-find.com, the password Genius standard is a good choice.

[Brett_Bleess]

For Excel files that need to be read by Reload Engine or Publisher, the Excel files will need to be password free, or one of the above workarounds could be used here, but what we have recommended in the past is to keep a non-password version of the file in an encrypted folder and protect the file that way with only the Qlik service account having access and the person that manages the file etc.  This becomes a problem though if there are constant changes to the Excel file, not sure of a good means to keep the non-password version updated in that case, but wanted to put this out here as well as another potential solution.

Regards,
Brett

[sujenss]

Things like forgetting passwords often happen. When a hard-working worksheet can no longer be changed because of a forgotten password, you must feel depressed? don't worry youc an try remove Excel password with VBA

1. Click "Development Tools" in the menu bar, select Record Macro, and record a macro file named "Clear Sheet Protection Password" (you can name the macro file according to your own habits).

2. Click "Development Tools" in the menu bar again, select "Macro" to open the macro dialog box, and select "Macro Name" for the newly created macro file. Then click "Edit" to open the macro (VBA) editing window.

3. On the left side of the macro (VBA) editing window, select "VBAProject PERSONAL, XLSB" → "Module" → "Module 2" in turn. After deleting the original content in the code window on the right, copy the following code. Paste and save in the code window.

4. Click "Development Tools" in the menu bar again, select "Macro" to open the macro dialog box, "Macro Name" select the newly created macro file, and then click "Execute" or "Single Step", and wait for the program to complete After the worksheet protection password is cleared. (Hint: The program is a bit stuck when the program is running. If the computer configuration is low, single-step execution is selected. The effect is the same.)

5. Copy the code and past it and run it. then it will remove Excel password.

source: https://www.passcue.com/remove-password-protection-from-excel.html https://yodalearning.com/tutorials/remove-password-excel-using-vba-macros/