How to Load password protected excel file

[raju_insights]

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.

[trdandamudi]

Please follow below links to get a solution..

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

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

[Ronnie_Taborn]

Did you find a solution? 

[raju_insights]

Hi Ronnie Taborn,

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

[Anil_Babu_Samineni]

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

OR

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$`;

[raju_insights]

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.

[raju_insights · Accepted solution]

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.