Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Techies,
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.
Please follow below links to get a solution..
Did you find a solution?
Hi Ronnie Taborn,
Working on that. I am not well versed in using macros. It will be great if someone can help with macros.
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$`;
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.
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.