Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

load a Password protected Excel File to QlikView

Hi Guys,

Is it possible to load a Password protected Excel File to QlikView?

Thanks,

Aldo.

Tags (3)
1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: load a Password protected Excel File to QlikView

Yeah sure.

I take it you have already created an excel driver ODBC connection, which you should be able to select in QV script using the Connect... button.  This will generate something like ...

ODBC CONNECT32 TO [Excel Files;DBQ=U:\QV\Sandpit\ProtectedFile.xls];

... where "Excel Files" is the name of the DSN and the filepath (DBQ) is the source file.  If the file isn't already open the ODBC wizard will fail, so open the Excel file first.  After that use the Select wizard to generate the SQL code directly below the ODBC code, something like ...

SQL SELECT *

FROM `U:\QV\SANDPIT\Protected`.`Sheet1$`;

flipside

19 Replies
mov
Esteemed Contributor III

load a Password protected Excel File to QlikView

No
(I wish someone proves me wrong...)

Not applicable

load a Password protected Excel File to QlikView

what about the new version 10?

It is pretty strange...

mov
Esteemed Contributor III

load a Password protected Excel File to QlikView

Actually, I tried in QV10. It says:
The Excel file can't be read because it is encrypted.
Doesn't leave much hope.

MVP & Luminary
MVP & Luminary

load a Password protected Excel File to QlikView

Hi there,

I have managed to find a work around for this one. If you create an ODBC connection to the spreadsheet and use that for the connection string then it should still correct - even with the protection. On clicking the Select button to load the data you need to tick the 'System Tables' check box in order to be able to see the protected sheets.

Once you have done this you should be able to select data from your protected Excel document.

Unfortunately this means you don't get all the wizards you generally get when importing Excel data and you need to have the Excel ODBC drivers on the machine you are doing the load.

Hope this helps,

Regards,
Steve

mov
Esteemed Contributor III

load a Password protected Excel File to QlikView

Steve - great idea, thanks!
I'll defenitly try it.

Not applicable

load a Password protected Excel File to QlikView

Nice idea!

It works? did someone tried it?

Thanks,

Aldo.

Not applicable

load a Password protected Excel File to QlikView

Hi Steve,

I tried and it works, but only while the excel file is open.

Is there any workaround?

Thanks in advance,

Aldo.

Not applicable

Re: load a Password protected Excel File to QlikView

Hi Michael,

Did you find a way to implement this issue?

Is it possible in QV11 SR2 ?

Thanks,

Aldo.

flipside
Valued Contributor II

Re: load a Password protected Excel File to QlikView

Hi Aldo,

The following functions seem to work ...

Function OpenExcel(filepath,fext,pwd1,pwd2)

    set objExcel = CreateObject("Excel.application")

    set objWB = objExcel.Workbooks.Open(filepath & fext,,,,pwd1,pwd2)

'.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword,

'            IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)

end Function

Function CloseExcel

    on error resume next

    set xl = getobject(,"Excel.Application")

    xl.activeworkbook.saved = true

    xl.Quit

    set xl = nothing

    call CloseExcel

End Function

... and call in script with the following ...

let x = OpenExcel(filepath,fext,pwd1,pwd2);

ODBC CONNECT32 TO $(cmdODBC);

SQL $(cmdSQL);

DISCONNECT;

let x = CloseExcel();

NOTE: The CloseExcel() function will close all instances of Excel open on the machine.

flipside