Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

badelma23
New Contributor

Macros for dynamically export table objects to Excel

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.