Can QV Externally Update Excel Data?

[#1]

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.

[#2]

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

[#3]

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.

[#4]

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.

[jonathandienst]

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

[#6]

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.

[#7]

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.

[flipside · Accepted solution]

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

10

20

25

33

41

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

[#9]

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.

[flipside]

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