topic How to open Password protected Excel file in qlikview? in QlikView

How to open Password protected Excel file in qlikview?

siva_boggarapu — Fri, 03 Feb 2012 06:48:13 GMT

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.

How to open Password protected Excel file in qlikview?

Anonymous — Fri, 03 Feb 2012 08:16:57 GMT

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

How to open Password protected Excel file in qlikview?

siva_boggarapu — Fri, 03 Feb 2012 09:36:03 GMT

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

best

Siva

Re: How to open Password protected Excel file in qlikview?

Anonymous — Tue, 31 Jul 2012 11:35:34 GMT

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

Re: How to open Password protected Excel file in qlikview?

bimala0507 — Mon, 29 Jun 2015 06:52:21 GMT

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

Re: How to open Password protected Excel file in qlikview?

Anonymous — Tue, 05 Dec 2017 08:16:50 GMT

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?

Re: How to open Password protected Excel file in qlikview?

Zmirell — Tue, 29 Jan 2019 02:06:29 GMT

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!

Re: How to open Password protected Excel file in qlikview?

SeanMoss — Wed, 13 Mar 2019 02:42:55 GMT

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.

Re: How to open Password protected Excel file in qlikview?

Brett_Bleess — Wed, 20 Mar 2019 20:47:25 GMT

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

Re: How to open Password protected Excel file in qlikview?

sujenss — Mon, 02 Dec 2019 10:08:11 GMT

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/

Re: How to open Password protected Excel file in qlikview?

sayanimondal — Thu, 14 Dec 2023 12:47:02 GMT

Opening password-protected Excel files in QlikView can be achieved in several ways, depending on your specific situation and desired outcome. Here are some options:

1. Open the Excel file before loading data:

This is the simplest method if you only need a few sheets or specific data points from the Excel file.
Open the Excel file outside of QlikView and enter the password.
Copy the desired data or sheets.
Paste the copied data into your QlikView script using functions like ClipData or Clipboard.

2. Use the OpenExcel function:

This function allows you to directly load data from the password-protected Excel file within your QlikView script.
Specify the file path, sheet name (optional), and password in the function call.
Ensure the System Tables checkbox is checked when selecting the data source in the Script Editor. This enables access to the password prompt.
Enter the password when prompted during script execution.

3. Utilize the ODBC connector:

This method offers more flexibility but requires additional configuration.
Install and configure a third-party ODBC driver that can access password-protected Excel files.
Define an ODBC connection within your QlikView script using the ODBC CONNECT statement.
Specify the connection parameters, including the driver name, file path, and password.
Use the connection in your script to load data from the Excel file.

4. Automate the password entry with a batch file:

This advanced method involves creating a batch file that automatically enters the password when QlikView attempts to access the Excel file.
This requires scripting knowledge and specific system configurations.
Consult QlikView documentation or community forums for detailed instructions on this approach.

Additional considerations:

Security: Store passwords securely, such as in QlikView environment variables or external encryption tools.
Performance: Opening password-protected files with OpenExcel can be slower than other methods.
Permissions: Ensure you have proper access rights to the Excel file and ODBC driver.

Choosing the best approach depends on your specific needs, technical skills, and security requirements. If you're unsure, consult the QlikView documentation or seek help from the QlikView community for further guidance. or you can also visit our website game development course for more details