1 Reply Latest reply: Jul 7, 2016 7:53 AM by Marcus Sommer RSS

    Export full object to excel

    shiva ganguri

      Hi Friends,

       

      I am trying to export the chart from Qv to excel.

       

      I am getting only few rows from the chart.how to export full table with all the values?

       

      I am using the below macro

       

      Call CopyToOpenExcelDocument("RECAP_CANAUX"     , "FullTable", 2, "RECAP_CANAUX"     , ExcelApp, "Values")

       

       

      Sub CopyToOpenExcelDocument( CopyObject , CopyType, CopySheet, PasteRange, ExcelApp, PasteStyle)

       

          Set O_Curr_Object = ActiveDocument.GetSheetObject(CopyObject)

          ActiveDocument.GetApplication.WaitForIdle

       

        Select Case CopyType

        case "Text"

                 O_Curr_Object.CopyTextToClipboard

          case "Image"

            O_Curr_Object.CopyBitmapToClipboard true

        case "Values"

          O_Curr_Object.CopyValuesToClipboard

          case "Table"      

            O_Curr_Object.CopyTableToClipboard false

          case "FullTable"      

            O_Curr_Object.CopyTableToClipboard true

          End Select

       

          ExcelApp.ActiveWorkbook.Sheets(CopySheet).Select

       

          ExcelApp.ActiveWorkbook.Sheets(CopySheet).Range(PasteRange).Select

         

          Select Case PasteStyle

          case "Values"

            ExcelApp.ActiveWorkbook.Sheets(CopySheet).PasteSpecial 1

          case "Normal"

            ExcelApp.ActiveWorkbook.Sheets(CopySheet).Paste

          End Select

       

       

      End Sub

        • Re: Export full object to excel
          Marcus Sommer

          I had had recently discussed several similar cases with another community member and we found several different reasons. So it's important that sheets and objects will activated each time, that WaitForIdle and Sleep statements are implemented to avoid that some parts will be execute before others are finished.

           

          But this won't help in each case - sometimes there are conflicts with the clipboard by which sometimes content will be lost or even added - very strange but sometimes it happend. Helpful could be to clear the clipboard and/or the cache from qlikview:

           

          Re: Failed to copy to clipboard

          Re: copy clipboard failed

           

          ActiveDocument.ClearCache

           

          or depending on the used RAM:

           

          For Each o in GetObject("winmgmts:\\.\root\cimv2").ExecQuery("Select FreePhysicalMemory from Win32_OperatingSystem")

               memory_size = o.FreePhysicalMemory

          Next

          if memory_size < 3000000 then

               ActiveDocument.ClearCache

          end if

           

          - Marcus