Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarahallen1
Creator II
Creator II

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

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.

23 Replies
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
Contributor III
Contributor III

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
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III

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
Creator II
Creator II
Author

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
Champion
Champion

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
Creator II
Creator II
Author

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

error.png

qvw script.png

qvw edit module.png

file location.png

sarahallen1
Creator II
Creator II
Author

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
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II
Author

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