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:"



            ActiveDocument.GetSheetObject("CH177").CopyTableToClipboard true

            XLSheet.Paste ' -4163    'values

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


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


            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



      Dim obj as string.


      if getVariable(vNA=1 and vCurrentDimensionId=1)

      then obj="CH198"


        getVariable(vNA=1 and vCurrentDimensionId=2)

      then obj="CH203"


        getVariable(vNA=1 and vCurrentDimensionId=4)

      then obj="CH204"


        getVariable(vNA=1 and vCurrentDimensionId=3)

      then obj="CH205"


        getVariable(vNA=1 and vCurrentDimensionId=8)

      then obj="CH202"


        getVariable(vNA=1 and vCurrentDimensionId=8)

      then obj="CH202"


        getVariable(vNA=1 and vCurrentDimensionId=9)

      then obj="CH206"

      end if


      Any suggestion would be very much appreciated.


      Best regards.