2 Replies Latest reply: Aug 14, 2012 2:37 PM by mee1d3hs RSS

    Export data to excel - Automatically or in script

      How would I go about automating exportign data to excel from multiple tables. I have a complex dashboard that profiles customers and say after I selected a specific group of customers I wanted to export the data for all charts to excel, maybe to develop a powerpoint presentation as printed reports are not that good in Qview

       

      Can someone share some sample code,and also where would I type that code into and how would it launch? I see that a button can have actions but the export does not seem to allow excel, it appears the button can trigger code - is this how I would do this?

        • Re: Export data to excel - Automatically or in script
          Rebecca Molstad

          This link may be a good starting point. You can adjust the code to export your reports as needed.

           

          http://community.qlik.com/message/242735#242735

            • Re: Export data to excel - Automatically or in script

              Thanks,

               

              I was able to do it in a test way, as follows which may be similar to what you suggeted (I can't see the qvw as for now I have an evaluation version). The code is modified abit from somethign I found, it adds a prompt for the location to save it

               

              Sub to_excel

              Dim wsname

              Set XLApp = CreateObject("Excel.Application")

              XLApp.Visible = TRUE

              Set XLDoc = XLApp.Workbooks.Add

              wsname = ""

              wsname = "Sheet1"

               

              strFileName = InputBox("Enter Path and File Name (from My Documents)", "Enter Path and Filename", defaultValue)

               

               

              strFileName = "C:\Documents and Settings\ewnym5s\My Documents\" & strFileName

              ActiveDocument.ClearAll

              XLDoc.Sheets(wsname).activate

              ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

              XLDoc.Sheets(wsname).Range("A" & 1).Select

              XLDoc.Sheets(wsname).Paste

              ActiveDocument.GetApplication.WaitForIdle

               

              wsname = "Sheet2"

              XLDoc.Sheets(wsname).activate

              ActiveDocument.GetSheetObject("CH34").CopyTableToClipboard true

              XLDoc.Sheets(wsname).Range("A" & 1).Select

              XLDoc.Sheets(wsname).Paste

              ActiveDocument.GetApplication.WaitForIdle

               

              XLDoc.SaveAs(strFileName)

               

              XLDoc.Close

              end sub

               

               

              I have a further question that is really a visual basic question,

               

              I do not want to have 25 or 30 blocks of code one for each chart - that is what for next loops were invented for

               

              I do not know in vb if you can and how to cycle through values that are not sequential (from a list like CH01, CH03, CH04) is that possible in your knowledge? can i ask how (as opposed to searching for it - whihc i admit is lazy)

               

              Also, in qview, my charts are in multiple sheets, do I need to switch sheet to reference charts in another one or will qview find CHXX wherever it may be

               

              Thanks