14 Replies Latest reply: Nov 29, 2012 11:01 AM by Steven Bain RSS

    Can QV Externally Update Excel Data?

    Steven Bain

      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?

          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?
              Steven Bain

              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?

                  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?
                      Steven Bain

                      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?
                      Jonathan Dienst

                      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?
                          Steven Bain

                          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?
                              Dave Riley

                              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

                                • Re: Can QV Externally Update Excel Data?
                                  Steven Bain

                                  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?
                                      Dave Riley

                                      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