I want to load the FORMULAE from Excel cells not the values

[sarahallen1]

Is this possible?  I want to define fields etc in my LOAD statement as usual, but for the field values I want to read in the formula in each Excel cell, rather than the value.

Any ideas would be very welcome!

Thanks.

[avinashelite]

Hi Sarah,

unfortunately this is not possible in Qlikview ..you need to add a column using =FORMULATEXT(A1) and use this column in QV.

[ravishankarqv]

One idea that comes to my mind is writing VBS that take excel file path as input and save that with "show formulas" option selected into a new temporary file in CSV.  Use this macro inside QlikView to create a temporary CSV file with exposed formula. Finally, load temporary file instead of original excel file

HTH

Ravi

[petter]

There is actually a way of getting the formulas in a spreadsheet. It is "cumbersome" and you will have to invest some time into implementing it. The prerequisite for what I am suggesting is that all the Excel files are OOXML ... the new Excel format that has XLSX-extension.

1) Iterate through all ~500 Excel-files

2) For each XLSX-file use the Unzip-routine I have suggested in a previous article.

      How to extract Sheet Names from an Excel XLSX-file without ODBC

3) Then for each sheet that was extracted by UnZip - it will be an XML-file which contains both the value, formula and sheet reference for all cells.Read these into a table in QlikView with the necessary information.

[ravishankarqv]

In general there is an EXTENDED PROPERTY named Allow Formulas=True/False in OLE DB connection string that lets you access EXCEL formulas as text or evaluated values.

If you figure out how to do that in Connection String, your problem will be solved.

HTH

Ravi

[sarahallen1]

Thanks Ravi I will have a go.  I am not used to using macros so may have to find my way a bit!  Thanks

[tamilarasu]

Hi Sarah,

I would suggest you to create an Excel vba to replace all the formulas (like "= ) by looping through the spreadsheets. By doing so, you need not to open each and every spreadsheets manually. Also you will not find any difficulties while uploading the spreadsheets into Qlikview.

Hope this helps you.

If you can't find an excel macro, let me know.

[sarahallen1]

Thanks Petter, this looks promising.

I am not very familiar with macros so am finding my way through this a bit!

But I'm trying first of all just to do a single .XLSX sample file with step 2 (your unzip solution).

This may be a silly question, but does the VB script for the unzip function need to be in the QVW's Edit Module, or in the VB of the .XLSX file?  I tried to put it in the XLSX but it wanted me to save as a XLSM as expected, then I tried to run the QVW with the XLSM file and it failed.  I also tried to leave the XLSX module unchanged, and instead put it in QVW's Edit Module, but it still failed.  Both times I get the error below. 

Where am I going wrong?

Thanks

[sarahallen1]

Thanks Tamil.  It sounds like a good solution (ideally I don't want to have to create new files but if it's unavoidable then so be it!!).  With this sort of solution, would it stay 'live'?  The set of files changes regularly so the corresponding '= versions of each file would need to be refreshed each time the QVW loaded to check for the formulae.

[petter]

Hi Sarah,

The VBScript has to be in the QVW's Edit Module. I might have a go at it later this evening to see if I can put together a small sample app for you that illustrates how to do it completely.

[sarahallen1]

This sounds great!  How do I use the property?  I've never done an OLE DB connection to an Excel file before.