topic Re: How to Load password protected excel file in QlikView

How to Load password protected excel file

rajuamet — Wed, 25 Nov 2020 16:16:04 GMT

Hi Techies,

Lets say I have an excel file named 'Protected_1.xls' in desktop.
I have created ODBC connection for that excel file.
The password is 1234.
How to load this excel file.
In various discussions it is mentioned that the excel file should be kept open when loading.
Is there any way to automatically open the excel file, load the data and automatically close the excel file during each reload.

Re: How to Load password protected excel file

trdandamudi — Mon, 12 Sep 2016 14:10:33 GMT

Please follow below links to get a solution..

https://community.qlik.com/thread/13917

https://community.qlik.com/ideas/3129

Re: How to Load password protected excel file

Ronnie_Taborn — Mon, 12 Sep 2016 14:49:33 GMT

Did you find a solution?

Re: How to Load password protected excel file

rajuamet — Tue, 13 Sep 2016 05:54:22 GMT

Hi Ronnie Taborn,

Working on that. I am not well versed in using macros. It will be great if someone can help with macros.

Re: How to Load password protected excel file

Anil_Babu_Samineni — Tue, 13 Sep 2016 07:42:21 GMT

Try This - This might help ful or work. But, If your excel has read only it doesn't work. But try luck and let me know

1) How to check whether this file is Read Only - Try to Ctrl + S then see. If file saves the success then we can easily connect with out Macro. Please Confirm me this

Dim objExcel

Set objExcel = CreateObject("Excel.Application")

objExcel.Application.DisplayAlerts = False

objExcel.Application.Visible = True

objExcel.Application.Workbooks.Open "QLIK:\PWD.xls", "3", True, , "a"

objExcel.Application.ActiveWorkbook.Unprotect

objExcel.Application.ActiveWorkbook.SaveAs "QLIK:\UNPWD.xls", , ""

objExcel.Application.DisplayAlerts = True>

objExcel.Application.Quit

I just connected thru Oracle. From Data tab of the bottom - Choose ODBC & Forced 32 Bit (Check) - Connect -- Choose Excel Files give only password -- Then choose your path of the protected Excel File -- Then Hit OK -- Then go to back and now select the Data from Select Button -- Might be Hit OK -- Should work

ODBC CONNECT32 TO [Excel Files;DBQ=C:\USERS\Tal\DESKTOP\Copy of PWD.xlsx] (XPassword is ABCDEF);

SQL SELECT Sales

FROM `C:\Users\Tal\Desktop\Copy of PWD.xlsx`.`Sheet1$`;

Re: How to Load password protected excel file

rajuamet — Tue, 13 Sep 2016 12:07:03 GMT

Hi Anil babu,

My excel has no restrictions. I do not want to Unprotect and save the file again. But anyway I have figured out a work around.

Re: How to Load password protected excel file

rajuamet — Tue, 13 Sep 2016 12:28:40 GMT

Hi Ronnie Taborn,

I have found a solution (workaround):

Step 1: Open protected excel in back-end using VBScript Script File

set XLApp = CreateObject("Excel.Application")

XLApp.Workbooks.Open "C:\USERS\RANGARAJU.RAJAPPAN\DESKTOP\Protected_1.xls",,,,"1234"

XLApp.Visible = False

In notepad copy the above script and save as Hack.vbs

Step 2: ODBC connection for Protected_1.xls in qvw

ODBC CONNECT TO [Excel Files;DBQ=C:\USERS\RANGARAJU.RAJAPPAN\DESKTOP\Protected_1.xls];

SQL SELECT *

FROM `C:\USERS\RANGARAJU.RAJAPPAN\DESKTOP\Protected_1.xls`.`Sheet1$`;

Exit Script;

Save it as Load Excel.qvw

Step 3: Kill EXCEL.EXE using bat file

Taskkill /IM Excel.Exe /f

EXIT

In notepad copy the above script and save as Kill.bat

Step 4: Creating a batch file to execute all in sequence

START Hack.vbs

"C:\Program Files\QlikView\qv.exe" /r "C:\Users\rangaraju.rajappan\Desktop\SetUp\Load Excel.qvw"

START Kill Excel.bat

In notepad copy the above script and save as Execute.bat

Now run the Execute.bat to test.

Note: Replace the paths in respective places.