0 Replies Latest reply: Mar 13, 2018 1:17 PM by Del Del RSS

    Macros for dynamically export table objects to Excel

    Del Del

      Hello everyone,

      I'm not totally new to Qlik, but I'm new to VBA scripts. I try to export a table object to Excel, which works fine based on some examples I found, but what i try do to is dynamically pass the table object id. The table object id that i want to export changes based on some selections. For example if i choose Var1 = 1 and Var2=1 then show tableID1 etc. Wehn i press the export button that run the macro i want to export the table object that is shown.

       

      Here is a part of my code:

      sub ExportWithSelections

            Set XLApp = CreateObject("Excel.Application")

            XLApp.Visible = True 

            Set XLWorkbook = XLApp.Workbooks.Add

            Set XLSheet = XLWorkbook.sheets("Sheet1")

            XLSheet.Range("J1")="Selected Filters:"

            XLSheet.Range("A1").Select

           

            ActiveDocument.GetSheetObject("CH177").CopyTableToClipboard true

            XLSheet.Paste ' -4163    'values

            XLSheet.Range("B:F").NumberFormatLocal = "#.##0"

            XLSheet.Range("J2").Select


      rem ** selection of the table on=bject id**

            ActiveDocument.GetSheetObject("TX1553").CopyTextToClipboard

            XLSheet.Paste ' -4163    'values

            XLSheet.Paste ' -4163    'values

            XLApp.Selection.WrapText = false


            set XLSheet = nothing

            set XLWorkbook = nothing

            set XLApp = nothing


      end sub


      I try to include something like this but it seems like the syntax is not correct. getVariables is a function that i use to determine the variables values so i can decide which table object id i should pass to the method.

      ----------------------------------------------


      FUNCTION getVariable(varName)

      set v = ActiveDocument.Variables(varName)

      getVariable = v.GetContent.String

      END FUNCTION


      ------------------------------------------

      Dim obj as string.

       

      if getVariable(vNA=1 and vCurrentDimensionId=1)

      then obj="CH198"

      elseif

        getVariable(vNA=1 and vCurrentDimensionId=2)

      then obj="CH203"

        elseif

        getVariable(vNA=1 and vCurrentDimensionId=4)

      then obj="CH204"

      elseif

        getVariable(vNA=1 and vCurrentDimensionId=3)

      then obj="CH205"

        elseif

        getVariable(vNA=1 and vCurrentDimensionId=8)

      then obj="CH202"

         elseif

        getVariable(vNA=1 and vCurrentDimensionId=8)

      then obj="CH202"

          elseif

        getVariable(vNA=1 and vCurrentDimensionId=9)

      then obj="CH206"

      end if

       

      Any suggestion would be very much appreciated.

       

      Best regards.