topic Re: Can QV Externally Update Excel Data? in QlikView

Can QV Externally Update Excel Data?

Wed, 14 Nov 2012 19:20:28 GMT

Hiya Guys,

I have an issue where I would like to import Excel data into QV using a Load Script, then change the data in some of the cells in the Excel Spreadsheet, and then import the new data.

We have a spreadsheet which has many dependent formulae on it - and manually changing just one cell updates the whole spreadsheet. Instead of having to do this manually - is it possible to automate the process from a QV Load Script?

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

Wed, 14 Nov 2012 20:26:12 GMT

Hi Steve,

If I understand it correctly, you are loading data from an Excel file (or any source) at a given instance.

But once the data is modified, the changes you have made must be reflected in your QlikView application.

If this is the case, you can try implementing Incremental Load to suit your requirement. This will allow you to fetch the modified data to the application assuming that you are making the changes in the Excel file against some comparable field like a record_add_date or record_update_date or some key value, etc.

Is that what you are looking for?

Regards,

-Khaled

Re: Can QV Externally Update Excel Data?

Thu, 15 Nov 2012 10:07:01 GMT

Hiya khaledshahbaaz,

Thank you for your response - unfortunately, this is not quite my issue.

I have an Excel spreadsheet, for example, with the word "Actual" in Cell A1. All of the formulae reference this Cell and dynamically calculate figures based on the fact that it contains the word "Actual" (not ideal, I know, but who's to question Accountants?!) 🙂

I need to load this data into QV - which works, and is not a problem.

However, I then need to add to this data the "Budget" figures. These are calculated using the same Spreadsheet, but by changing Cell A1 to contain the word "Budget" - all the formulae then recalculate.

This is a simple example. The real-world example would involve around 30 sheets, each requiring both Actual and Budget figures.

I am wondering if there is a way for QV to, effectively, load the Actual data - then open then Excel file, update the contents of Cell A1, save the new formulae/values, and then load (concatenate) the Budget data.

Is this possible?

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

Thu, 15 Nov 2012 12:21:26 GMT

Hi Steve,

I see it now. You are trying to, in a way, swap values for a particular cell so that each time, the Excel calculates according to the value you have swapped.

If calculations at Excel sheet were not involved, you could simulate the structure of your Excel file using a table box and export it everytime and reload from the Export file conditionally tweaking the load at script. I do not see any direct way of doing this or so does my limited understanding of QlikView suggests. Perhaps, using a macro might help.

You can try to share a sample file here. I'm sure the community experts wouldn't disappoint you.

Good Luck

Regards,

-Khaled.

Re: Can QV Externally Update Excel Data?

jonathandienst — Thu, 15 Nov 2012 12:26:19 GMT

Steve

You could use a partial load for this purpose. A partial load can optionally add to an existing table. Check out partial loads in the reference manual.

You would need manually change the value of A1 and then run a partial reload for each value. You could automate the process, but it would involve an amount of script or VBA programming to do so.

Regards

Jonathan

Re: Can QV Externally Update Excel Data?

Thu, 15 Nov 2012 12:41:57 GMT

Hiya khaledshahbaaz,

Cheers - unfortunately the Excel sheet is too complicated to recreate in QV, and the data is sensative and not something which I can share (and, with it being so complex, is not something I can really mock-up).

It's looking though I cannot manipulate the Excel file externally from QV - so a new Spreadsheet will have to be created for each dataset 😞

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

Thu, 15 Nov 2012 12:46:08 GMT

Hiya Jonathan,

Thanks for your response. The Partial Load side of things is not an issue - it's more automating the change of Excel data which is the problem.

Ideally I want QV to be able to update the Excel file, allow it to recalculate, save, and then load the new values... but so far this doesn't seem possible 😞

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

flipside — Thu, 15 Nov 2012 13:21:57 GMT

Here's a (Module) function (altering cell B1 in 1st sheet) ...

Function excelFormulaReader(file,value_submit)

set objExcel = CreateObject("Excel.application")

objExcel.DisplayAlerts = False

set objWB = objExcel.Workbooks.Open(file)

With objWB.Worksheets(1)

.Activate

.Cells(2,1).Value = value_submit

End With

objExcel.save

objExcel.quit

set objSheet = Nothing

set objWB = Nothing

set objExcel = Nothing

end function

.. and to call it in the load script, something like ...

ValueRange:

LOAD * INLINE [

Seed

50];

For i = 0 to NoOfRows('ValueRange')-1

let value = peek('Seed',$(i),'ValueRange');

let excelFormulaReader = excelFormulaReader(' your file path ',$(value));

Data:

LOAD $(value) as Seed,

+ your select data code;

Next;

flipside

Re: Can QV Externally Update Excel Data?

Tue, 20 Nov 2012 09:35:35 GMT

Hiya flipside,

Thank you for this! I've finally tested it and it works. Just one note to anyone else who comes across this: you need to have MS Excel installed on the desktop/server you run the Load Script from.

If you have time to reply - just a couple of questions:

1) Why do you have to use the format "let excelFormulaReader = excelFormulaReader..." and not just "Call excelFormulaReader..."? (which doesn't work);

2) I would like to make this dynamic within my Load Script - is there any way of writing User Functions within a Script?... I can't seem to find any examples, eg:

Sub UpdateExcelCell(xlsFile, val, ws, col, row)

Let excelFormulaReader = excelFormulaReader(xlsFile, val, ws, col, row);

End Sub

My hope to be able to then call this Function serveral times (with different parameter values) from within my Load Script... is this possible?

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

flipside — Tue, 20 Nov 2012 11:13:20 GMT

Hi Steve,

It is possible to read and amend Excel files without installing MS Office (http://stackoverflow.com/questions/253394/how-to-modify-a-spreadsheet-without-excel-using-vbscript), but I haven't tried this myself. Installing Excel is the easier option it seems.

AFAIK the Call command only calls subroutines declared in the Load script, and won't reference a Module function. I haven't tried wrapping a function in a subroutine, however your example just passes values to the sub to pass to the function - my code already is dynamic, just use the LET command when needed, you should be able to add the extra parameters to the function along the lines of this (untested) code...

Function excelFormulaReader(file,value_submit, wsheet,cellcol,cellrow)

set objExcel = CreateObject("Excel.application")

objExcel.DisplayAlerts = False

set objWB = objExcel.Workbooks.Open(file)

With objWB.Worksheets(wsheet)

.Activate

.Cells(cellcol,cellrow).Value = value_submit

End With

objExcel.save

objExcel.quit

set objSheet = Nothing

set objWB = Nothing

set objExcel = Nothing

end function

Hope this works.

flipside

Re: Can QV Externally Update Excel Data?

Tue, 20 Nov 2012 11:20:51 GMT

Thanks flipside,

Looks like a useful VB Script! I haven't tried it but it's very useful to know that a solution is (may) be possible 🙂

This is exactly what I've done, although I think I was trying to just add another (unnecessary) step! 🙂

I would like to create a Subroutine/Function within the Load Script which accepts parameters... is this possible?

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

flipside — Tue, 20 Nov 2012 11:39:25 GMT

Do you mean like this (which works) ...

sub TestMessage(txt)

x = MsgBox(Upper(txt));

end sub

call TestMessage('HelloWorld');

flipside

Re: Can QV Externally Update Excel Data?

Thu, 29 Nov 2012 11:38:39 GMT

Thanks flipside,

However, I could not get this to work for some reason... I could not call the Function from another tab (or from within the same tab - for that matter!).

Cheers,

Steve.

Re: Can QV Externally Update Excel Data?

flipside — Thu, 29 Nov 2012 12:33:24 GMT

Strange!

Are you talking about the TestMessage sub which should be in the Load script or the earlier function which resides in the Module script?

Re: Can QV Externally Update Excel Data?

Thu, 29 Nov 2012 16:01:27 GMT

I'm refering to this example, above:

sub TestMessage(txt)

x = MsgBox(Upper(txt));

end sub

call TestMessage('HelloWorld');

I have tried this in the Load Script and it works... however I cannot get the same results when attempting to use the Excel function. It must be something I am overlooking, not to worry! 😄 I appreciate all your help.

Cheers,

Steve.